Flux rss
Search : in
By : Relevance Date Username
Statut : Not resolved

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

hardgay84, on Tuesday 29 July 2008 à 03:00:33
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
Configuration: Windows XP
Firefox 2.0.0.16
Reply to hardgay84  Report this message to moderators Go to last message

1


  • 1    This message seems useful, vote!
  • Ce message ne vous semble pas utile, votez !
  • Report this message to moderators
 Ivan-hoe, on Tuesday 5 August 2008 à 18:40:29
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
   
Reply to Ivan-hoe
Ask your question Reply

Résultats pour Cannot use ctrl+f in macro (need VBA help!!)

Can't use 2 sheets with a custom function. Hello, I am trying to make a Function in VBA. If I put the function in a Module in the Personal Macro Workbook I can't get it to show up on the custom function list. I wanted it to be in the personal workbook so I could use it in several... en.kioskea.net/forum/affich-26469-can-t-use-2-sheets-with-a-custom-function
Group/Move Rows to New Worksheet VBA Hello, I'm trying to find some VBA to do the following steps in excel: 1. Look down column A for similar values (will be in alpha order). 3. Group rows that have similar values in column A 4. Search for worksheet named with value in column A. 4a.... en.kioskea.net/forum/affich-30660-group-move-rows-to-new-worksheet-vba
Java and flash animation Hello I am building website I would like to put some flash animation on my website i don't know how to use flash macromedia is there some template that can be customize and placed instead ? Please revert back asap. Regards; Jinkazama en.kioskea.net/forum/affich-12657-java-and-flash-animation

Résultats pour Cannot use ctrl+f in macro (need VBA help!!)

Excel tips : How to insert date In an Excel cellExcel tips : How to insert date In an Excel cell Below are some tips on how to insert date and time in an excel cell for a specific purpose:- To insert current date, press “CTRL”+ “;” in the chosen cell. To insert current... en.kioskea.net/faq/sujet-313-excel-tips-how-to-insert-date-in-an-excel-cell
To insert an image in the FAQTo insert an image in the FAQ Method Where to find images? Inserting screenshots can help to understand an explanation. Here is how to improve tips on Kioskea (FAQ). For this trick, we opted to use the site Imageshack.us. Note:... en.kioskea.net/faq/sujet-67-to-insert-an-image-in-the-faq
[VB6/VBA] The CommonDialog control[VB6/VBA] The CommonDialog control The available functions with CommonDialog Getting started An example of a standard module The various functions of control CommonDialog Here is a list of all the available constants,although they... en.kioskea.net/faq/sujet-844-vb6-vba-the-commondialog-control
1 2 3 Next

Résultats pour Cannot use ctrl+f in macro (need VBA help!!)

Laptop keyboardHello, Some water was spilt on my friend's Toshiba laptop keyboard (on the left hand side; Shift, Ctrl, Fn, Home and Alt buttons) then the keyboard didn’t work. It seems like problem with the circuit inside. Just wondering whether I can change... en.kioskea.net/forum/affich-26762-laptop-keyboard
Water on laptop keyboardHello, Some water was spilt on my friend's Toshiba laptop keyboard (on the left hand side; Shift, Ctrl, Fn, Home and Alt buttons) then the keyboard didn’t work. It seems like problem with the circuit inside. Just wondering whether I can change... en.kioskea.net/forum/affich-28524-water-on-laptop-keyboard
Keyboard problemHello, Some water was spilt on my friend''s Toshiba laptop keyboard (on the left hand side; Shift, Ctrl, Fn, Home and Alt bottons) then the keyboard didn''t work. It seems like problem with the circiut inside. Just wondering... en.kioskea.net/forum/affich-18727-keyboard-problem
1 2 3 Next

Résultats pour Cannot use ctrl+f in macro (need VBA help!!)

Download OxygenOffice ProfessionalOxygenOffice Professional (was: OpenOffice.org Premium) is an enhanced version of OpenOffice.org what is a multi-platform office productivity suite. OxygenOffice Professional contains more extras like templates, cliparts, samples, fonts and VBA... en.kioskea.net/telecharger/telecharger-99-oxygenoffice-professional
1 2 3 Next

Résultats pour Cannot use ctrl+f in macro (need VBA help!!)

Base64 encoding The concept of Base64 encoding entails using US-ASCII (non-accented) characters to encode any sort of data in 8-bit form. Email protocols were originally designed to send messages in plain text only. But as email systems vary greatly, transferring... en.kioskea.net/base/base64.php3
1 2 3 Next