Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Macro: find value and copy data (2003)

    On my worksheet, Column G is a named range called Provider. In my macro, my goal is to search column G for a provider name and if the value if found, then move the data beside that value to another location. A lot of this macro has been trial and error - mostly error. May I have a clue as to why the below code isn't working? It stops at IfColG.Value="a' Then...with the message "object variable or With Block variable not set".

    Dim ColG As Range
    SetColG = Range("Provider")
    lastrow = Cells(Rows.Count, "A").End(xlUp).Row

    If ColG.Value = "a" Then
    Range("A1:A6").Copy Cells(2, lastrow)

    End If

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro: find value and copy data (2003)

    There is a space lacking in the line

    SetColG = Range("Provider")

    It should be

    Set ColG = Range("Provider")

    (If you had had Option Explicit at the beginning of the module, the Visual Basic Editor would have warned you that SetColG was not defined)

    Apart from that, a multi-cell range does not have a value. You must either loop through the cells of the range or use the Find method to see if the value "a" occurs. For example:

    Dim oCell As Range
    Set oCell = ColG.Find(What:="a", LookIn:=xlValues, LookAt:=xlWhole)
    If Not oCell Is Nothing Then
    ...

    I'm not sure what the line

    Range("A1:A6").Copy Cells(2, lastrow)

    is intended to do. The (undeclared) variable lastrow is set to a row number, but you use it as a column number in Cells(2, lastrow). Even if you use Cells(lastrow, 2), I doubt it'll do what you want.

  3. #3
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro: find value and copy data (2003)

    Thanks Hans.

    Ouch - I still have a lot to learn. Back to the drawing board on this one.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro: find value and copy data (2003)

    Feel free to post more questions as needed.

  5. #5
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro: find value and copy data (2003)

    A little late between posts but this is a project I am working at leisurely as an attempt to teach myself how to code. So far, it's not working too well. I've discovered I don't know enough of the "language" in order to write code.

    In a nutshell, what I have been trying to do is this: I have 4 different values that repeat randomly in column G; the values are a, b, c, and d. If there is data in columns A:F beside any value of "a" in column G, I want to move it elsewhere. The same goes for the values of b, c and d. Part of my difficulty is figuring out how to say all of this is a macro. All I have managed so far is the following which simply moves one set of data but doesn't keep searching for other instances of "a" in column G:

    If Range("G1").Value = "a" Then
    Range("A1:F1").Copy
    Range("H2").PasteSpecial Transpose:=True
    End If

    I know that when I have figured out the above, I need to loop the whole thing but this middle bit has me baffled.

    Thanks for any help in advance.

    Deb

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro: find value and copy data (2003)

    If the data are repeating, where do you want to copy or move subsequent instances? Is there some kind of "rule" that determines where they go?

  7. #7
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro: find value and copy data (2003)

    The data is being moved to new columns...all data corresponding with "a" will be moved to column H; "b" will be moved to column I, "c" will be moved to column J, and "d" will be moved to column K. As you may have gathered from my previous post, I am taking the data from a row and transposing it to the correct column. Is it easier if I post a worksheet showing the current data and desired outcome?

    Thank you Hans.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro: find value and copy data (2003)

    Yes, that would be nice - make sure to alter sensitive data.

  9. #9
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro: find value and copy data (2003)

    Here is a sample of the data. Once I have the macro for moving the data around, I am going to add to the code (which I believe I can do!) to format the new header columns. I should note that in the (terrible) code that I already have, I have taken care of creating the new headers for each column...it's just moving the data that I can't figure out.

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro: find value and copy data (2003)

    Thanks. Here is a macro, with hopefully helpful comments.

    Sub TransferData()
    Dim lngSourceRow As Long
    Dim lngMaxRow As Long
    Dim lngRow As Long
    Dim lngTargetRow As Long
    Dim strTargetCol As String

    ' Last row to process
    lngMaxRow = Range("G65536").End(xlUp).Row
    ' Loop through the rows
    For lngRow = 1 To lngMaxRow
    ' Determine the target column
    Select Case Range("G" & lngRow)
    Case "a"
    strTargetCol = "H"
    Case "b"
    strTargetCol = "I"
    Case "c"
    strTargetCol = "J"
    Case "d"
    strTargetCol = "K"
    End Select
    ' Determine the first unused row in the target column
    lngTargetRow = Range(strTargetCol & 65536).End(xlUp).Row + 1
    ' Copy source row
    Range("A" & lngRow & ":F" & lngRow).Copy
    ' Paste special to transpose
    Range(strTargetCol & lngTargetRow).PasteSpecial Transpose:=True
    Next lngRow

    ' Remove blinking border
    Application.CutCopyMode = False
    End Sub

    See attached version of your workbook.

  11. #11
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro: find value and copy data (2003)

    Oh my....I don't think I would have written anything that resembled that. I also thought that I was going to use some sort of Do Loop when the data moving was determined. Thank you for this.

    May I ask a question? In all my readings to learn to write code, it usually says to start with the macro recorder as it's the best way to learn VBA. It may be helpful to learn small things like the syntax for formatting something etc., but how does one learn the gritty details i.e. writing something as you did for this example? I've been working at this problem on and off for a couple of months and have tossed out all kinds of different versions of code that in the end, didn't work. I was determined to solve this particular task and, after seeing your code, I don't think there is a hope in heaven that I could have "come up" with that.

    Sorry, I think my question turned into venting but the question still remains <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Macro: find value and copy data (2003)

    The macro recorder is indeed useful to learn what objects Excel VBA uses (Worksheet, Range, etc.), but by its very nature it cannot help you write loops, if ... then structures and the like. You have to learn that by studying a book or tutorial, or by attending an Excel VBA course. And, of course, by studying the questions and replies in this and similar forums.

    See <post#=539,691>post 539,691</post#> for some book recommendations.

    How to use Visual Basic for Applications in Excel contains links to Microsoft articles about Excel VBA.

    If you search Google for excel vba tutorial you'll find many online tutorials.

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Macro: find value and copy data (2003)

    The macro recorder, IMO, will no help you to code, especially things like looping, going thru collections, and especially making the specific code more generic.

    What it will do is help you to learn and see the "object model" and how it works in code.

    Depending on your needs, code written by the macro recorder could be 50-80% of the code you need. The rest will have to be modified and enhanced.

    Some things to be aware of:

    The macro recorder uses "Select" a lot which generally should be removed and the lines combined to make more efficient code. I estimate that nearly all "selections" are unneeded.

    When you use dialogs in the recorder, you will not get code for what you changed, you will get the settings from all the objects in the dialog. So unless you want to change all those settings with the code, you should remove all the items you did not change

    If you start off doing some simple things with the recorder and then look at the code, you can start to get a feel for what the code/objects are doing.

    Learning to do most complex things just takes some practice. There is plenty of code here for a variety of tasks, you can even just experiment .

    <post:=320,321>post 320,321</post:> has some links to some VB articles that you might find useful. I also recommend Excel Books by John Walkenbach (I have no affiliation whatsoever with him). His "poweprogramming" book is excellent.

    Steve

  14. #14
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro: find value and copy data (2003)

    Thanks to you and Hans for your sound advice.

    I think the practice part is key...there is so much to remember in coding that unless I keep at it continually, I tend to forget some of the language. Thank goodness for great forums like this one though!

  15. #15
    2 Star Lounger
    Join Date
    Dec 2004
    Location
    Stoney Creek, Ontario
    Posts
    196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Macro: find value and copy data (2003)

    Hello Hans.

    As I'm trying to read this code, I have two things I don't understand:

    1. In the line "Range("A" & lngRow & ":F" & lngRow).Copy", what does the &".F" refer to/do?
    2. The first line of code is Dim IngSourceRow as Long. I may not be seeing it or understanding properly, but lngSourceRow doesn't show up in the code anywhere else so I wondered why that line is there?

    Thanks for your patience!

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •