vba help needed

[prashshine]

Hi ,

I needed help with the following code

Set trgt = wt.Sheets("test").Range("a:a").Find(what:=4 / 1 / 2013, lookat:=xlWhole, LookIn:=xlFormulas).Offset(0, 7)

I am getting a runtime error 91 I tried the double quotes for 4/1/2013 . The A column has the formulas

Please help me on this...

Thanks

 

[s hal]

If you are trying to find the 4/1/2013 in the workbook and not in your vba code, I do believe you will need to use WorksheetFunction.Find() to accomplish this. That said, I'm not sure what you are hoping to accomplish so this may not be very helpful!

 

[prashshine]

Hi ,

I am trying to get the range using find method in VBA code. There is a column A where I have dates and these dates are achieved by using a formula. In this column I want to find a particular date i.e 4/1/2013 once found I just use the offset function to get the destination range where I actually need to paste the data.But when I run this code it gives me the run time error


Thanks again

 

[Rick Rothstein]

I needed help with the following code

Set trgt = wt.Sheets("test").Range("a:a").Find(what:=4 / 1 / 2013, lookat:=xlWhole, LookIn:=xlFormulas).Offset(0, 7)

I am getting a runtime error 91 I tried the double quotes for 4/1/2013 . The A column has the formulas

I would think this should work...

Set trgt = wt.Sheets("test").Range("a:a").Find(what:="4/1/2013", lookat:=xlWhole, LookIn:=xlFormulas).Offset(0, 7)

 

[prashshine]

Hi Rick,

I tried it earlier it wont work..

 

[Rick Rothstein]

Hi Rick,

I tried it earlier it wont work..

I tried it before I posted it and it worked for me. Try it again... if it still does not work for you, show us exactly what is in the cell you think it should match (copy it from the Formula Bar, not from what you see in the cell) and also show us the Cell Formatting for that cell (click the Custom item and show us what that format pattern is).

 

[prashshine]

The following is the code

Sub test()
Dim ws As Workbook
Dim wks As Worksheet
Dim wt As Workbook
Dim trgt As Range
Set wt = Workbooks("sample")
Set ws = Workbooks.Open("C:\Documents and Settings\Div\My Documents\dump")


Set trgt = wt.Sheets("test").Range("a:a").Find(What:="4/1/2013", LookAt:=xlWhole, LookIn:=xlFormulas).Offset(0, 7).Select
ws.Activate
ws.Sheets("Mob").Select
Cells.Find("DOM").Offset(2, 3).Select
trgt.PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, False, True
ws.Activate
Cells.Find("BRD").Offset(2, 3).Select
Range(Selection, Selection.End(xlToRight)).Copy
trgt.PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationAdd, False, True
Set trgt = wt.Sheets("test").Range("a:a").Find(What:="4/1/2013", LookAt:=xlWhole, LookIn:=xlFormulas).Offset(0, 8)
ws.Activate
ws.Sheets("Mob").Select
Cells.Find("DOM").Offset(2, 3).Select
trgt.PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationNone, False, True
ws.Activate
Cells.Find("BRD").Offset(1, 3).Select
Range(Selection, Selection.End(xlToRight)).Copy
trgt.PasteSpecial xlPasteValuesAndNumberFormats, xlPasteSpecialOperationAdd, False, True
wt.ActiveSheet.Next.Activate

End Sub



the column A in the sheet named test is the one where I need to find 4/1/2013. Cell a2 contains the date say 2/1/2013 then cell a3 contains the formula a2+1 and it continues. So when I select the cell containing 4/1/2013 i see a formula in the formula bar say a32+1.

 

[Rick Rothstein]

The following is the code

<<<snip>>>
Set trgt = wt.Sheets("test").Range("a:a").Find(What:="4/1/2013", LookAt:=xlWhole, LookIn:=xlFormulas).Offset(0, 7).Select
<<<snip>>>

the column A in the sheet named test is the one where I need to find 4/1/2013. Cell a2 contains the date say 2/1/2013 then cell a3 contains the formula a2+1 and it continues. So when I select the cell containing 4/1/2013 i see a formula in the formula bar say a32+1.

You have the LookIn argument set wrong... you want to search for the value, not the formula. Try it this way (you will need to make this change everywhere you are doing this kind of search)...

Set trgt = wt.Sheets("test").Range("a:a").Find(What:="4/1/2013", LookAt:=xlWhole, LookIn:=xlValues).Offset(0, 7).Select