Cannot use ctrl+f in macro (need VBA help!!)

[Closed]
Report
Posts
1
Registration date
Monday July 28, 2008
Status
Member
Last seen
July 28, 2008
-
Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
-
Hello,
I recorded a macro that when run, will perform a web query and extract daily prices for a particular commodity.
Now, I need to copy these prices automatcally into a new row each time corresponding to the particular date at which the prices are extracted for. I have already drawn a table with a list of dates in ascending order. How do i tell excel that if the date of the extracted data is eg 28 july 2008, it should copy the data and paste it beside the the 28 july 2008 in the table that I have drawn?

I have tried using ctrl -f when recording the macro but I get Run-Time error'91': Object variable or With block variable not set"

the code is below:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 29/07/2008 by Claude Cheng
'

'
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
Range("H1:N7").Select
Selection.ClearContents
Sheets("Copy Figures In Red").Select
Range("B2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("B13").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("B3").Select
Selection.Copy
Sheets("Palm Oil Prices").Select
Range("H2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H2").Select
Application.CutCopyMode = False
Selection.TextToColumns Destination:=Range("H3"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(Array(1, 4), Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1), _
Array(7, 1)), TrailingMinusNumbers:=True
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("H3").Select
Selection.Copy
Range("A10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("H9").Select
Application.CutCopyMode = False
Range("H9").Select
Selection.Copy
Range("C10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Range("H10").Select
Selection.Copy
Range("B10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A10").Select
Application.CutCopyMode = False
Application.CommandBars("Task Pane").Visible = False
Selection.Copy
Cells.Find(What:="28/07/2008", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("B10:C10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12").Select
Cells.Find(What:="28/07/2008", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Range("B18").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

1 reply

Posts
433
Registration date
Saturday February 16, 2008
Status
Member
Last seen
October 17, 2008
109
Hello Claude,
1- Make sure that you have the right format (neither "07/28/2008", nor "28/07/08)
2- Replace LookIn:=xlFormulas by xlValues
Cells.Find(What:="28/07/2008", After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

Ivan