I have the following code that loops thru and puts the word 'TEST' in column J if column B has a TEXT value of '020'
----------------------------------------------------------------------------------
With Range("J1:J" & Lastrow)
.Clear
Range(.Cells(2), .Cells(.Count)).Formula = "=if((b2)=""020"",""TEST"","""")"
End With
----------------------------------------------------------------------------------
Questions
1) why do I need double quotes "" "" on every argument in the function? Is it because I'm working with TEXT data?
2)I want to use multiple ifs (ie if cell b2 equals '020' OR '030' then put the word 'TEST' in col J). How to change the syntax to do this?
In excel macro, I am trying to input data into sql server from excel ADO what is the correct syntax for an insert statement using string sql? This table has two columns supplier ID autonumber and desc as varchar.
Code: ' write data to txt file For j = 1 To 3 For k = 1 To 21 Print FNum, OPTOarray(j, k) Next k Next j
I am writing an Array to a text file but I keep getting an error that the Print statement requires a suitable object. I tried Debug but all this did was put the data into the immediate window and created a file that was empty.
"????.Print FNum, OPTOarray(j, k)".
The data in the immediate window is correct but when you open the text file with notepad there is nothing to be seen.
I´m writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)
Sub Copy_Sheet_Beta() Set wba = ActiveWorkbook On Error Resume Next If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt" Else Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls" End If Set wb = Workbooks("Projekt.xls") wb.Activate If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then MsgBox "overall doesn't exist!" Else........................................
I am currently using an Intersect statement in a worksheet module to perform two things: 1. Insert a time stamp into row 2 when row 1 has a price inserted 2.To clear that time stamp if the price is deleted at some later date.
My problem is with the time stamp value being deleted by the user. If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.
I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?
Im having problem with If/Then/Else Statements Not sure what the problem is. I have a text box a user inputs whatever in to create two new tabs one is the tab name then the next is tab completed. It isnt liking my syntax I have here.
I have a form and a combo box that I want ot fill the items in from cell F22.
Here is my Sheets(PowerAnalysis.xls).Cells("F22").Value = ComboBox2.Text What is the best way to get this done? I have searched here and on the web and I can't seem to find any straightforward answers.
I am trying to get the below autofilter working but to no avail, its just putting the text datStart and datEnd in the auto filter, not what I receive in the boxes, see bold line for line in question
Dim datStart, datEnd As Date Dim strAgent As String datStart = txtStart.Value datEnd = txtEnd.Value strAgent = txtAgent.Text Range("A4").Select Selection.AutoFilter Selection.AutoFilter Field:=4, Criteria1:=">datStart", Operator:=xlAnd, _ Criteria2:="<datEnd"
It doesn't work any more in Excel 2003. Of course it works directly such as: "WHERE (dbase_file.field1=' N0011') but I couldn't find the way to replace the ' N0011' with the parameter.
It is not the singular issue... "FROM dbase.file dbase.file " that worked well in Excel 2000, the Excel 2003 "wants": "FROM 'drivepath'dbase_file dbase_file " and I couldn' find a way to replace the explicit 'drivepath' with a predefined variable.
I am attempting to create a macro that opens a file named "shrinkage-billing.xls", searches for a variable "PTOSH" in column A, copies the adjacent cell and pastes the data in another worksheet named "Shrinkage Report 2009.xls"
Got a wee problem with some syntax for a copy function. At the moment the code i have copies the range and pastes to another range which is defined by a cell number. But i need it to paste special (values only).
I would like to know where can I find a good resource to learn about VBA syntax. I have VBA books and there are some examples of code in there but when I'm trying to read someoneelse's code I just get into commands or syntax I don't understand. For example I would like to be able to go some site and decifer what this line (or it's parts) means: If(cnt < MAXTEST, sDigSep & String(MAXTEST - cnt, "9"), "")
I have a variable array, that is, the first cell of the array is variable and the last cell is variable. I have dimmed the first cell , "firstcell" as a range. I have dimmed the last cell , "lastcell" as a range. I'd like to sort the array but first I have to select all cells in the array. Need the proper syntax to select all cells between "firstcell" and "lastcell" in my macro.
There are plenty of other columns, but these are key. The first column is the service type, of which there are 40. I want to have another worksheet that totals all the entries in the third column that don't say 'yes', for each service. So, in this case the first line of my new workbook would say: Nephrology1
If I copy "mysum" contents to a cell and replace "lista" with a defined list, it works just fine. But whenever I run this code, it gives me syntax error.
Sub aaa()Dim i As Integer, myvar As Variant, tester As VariantDim mysum As Variant, lista As Variant, alpha As Datelista = Sheet2.[D6].ValueWith Worksheets(Range("A10").Value) Do i = i + 1mysum = Evaluate("SUMPRODUCT(((ISNUMBER(MATCH($B$8:$B$10007," & lista & ",0))) *($A$8:$A$10007>(TODAY()-180))*(($E$8:$E$10007)+($F$8:$F$10007)+($G$8:$G$10007)+($I$8:$I$10007)+($K$8:$K$10007))))) myvar = Evaluate("=TIME(10,0,0)>" & mysum) Loop Until myvarEnd Withtester = 35 - Sheet2.[c10] alpha = mysumMsgBox "VALID for [" & lista & "] after " & i & " Day(s). hours in last 180 days after " & i & " Day(s) will be (" & alpha & ")"End Sub
I'm looking for syntax to break all links in a workbook using VBA. I've found the below through independent research but I'm receiving a type mismatch error.
Code: Sub BreakLinks() 'Macro Purpose: Break all links in the active workbook Dim vLinks As Variant
Always have problems getting my head round the syntax of the indirect function and am unable to find anything similar that's been asked.
I want to perform an operation on two numbers where the user selects which to use (add, subtract, multiply or divide) entered into another cell like this:
******** ******************** ************************************************************************>Microsoft Excel - 200701 - LCC.xls___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutC3C5= ABCD14+5*2****3Normal*9*4****5Indirect*#REF!*Sheet3* [HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.