Macro: find value and copy data (2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Thanks Hans.

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

Reply | Quote

Feel free to post more questions as needed.

Reply | Quote

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

Reply | Quote

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?

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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 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.

Reply | Quote

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 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

Reply | Quote

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!

Reply | Quote

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!

Reply | Quote

1) & is the concatenation operator that glues strings of text together. Say that lngRow = 3. The expression

"A" & lngRow & ":F" & lngRow

evaluates to

"A3:F3"

so the range A3:F3 will be copied to the clipboard.

2) Sorry about that, I forgot to remove that line. It is a remnant of an earlier version. You can delete the line without problems.

Reply | Quote