Reputation: 25
I am working on generating some automated reports. To reiterate, I would like my code to look at each "string" in Sheet1 column E (ignoring blanks), search for that value in another sheet/workbook, and paste the "single" contained in Sheet1 column D (ignoring blanks, if E is blank, D will be blank) to the left of the string found in the search earlier.
The receiving data sheets are widely dispersed and in a variety of formats, but the one constant is that each position for data input has a unique tag in an adjacent column that is also found in the primary data storage sheet. I don't have that much experience in VBA yet, and was wondering how some others would solve this problem in a dynamic way that wont break the minute a new column is added.
My current code is a jumbled mess, so any pointers, ideas, general strategies would be appreciated. I am trying to get away from using Sheets().select and other references like this, but I'm not sure how to do that yet.
Dim location As String
Dim rownum As Integer
Dim cellfinder As Integer
Sheets("Sheet2").Select 'Ensures that we start on sheet 2
rownum = Range("G2").Value
For cellfinder = 1 To rownum 'Loop goes for as many non-blank rows exist in column---- need
'to add code to skip over blank rows with offset function or else loop will not go on long enough.
Sheets("Sheet2").Select 'selects Pi tag data sheet
'hopefully adjusts the active cell relative to the loop count
If ActiveCell.Value = "" Then 'hopefully detects blank cells and skips to next loop
GoTo Skip
End If
location = ActiveCell.Value 'calls the location tag string the variable "location"
ActiveCell.Offset(0, -1).Select 'offsets from location tag to the "current value column"
ActiveCell.Value.Copy 'copies the value found in the current value column hopefully not the pi function
Sheets("Sheet1").Select 'Selects EOM sheet, can be whatever sheet we wish, or in another worksheet
Range("A1").Select 'establishes a starting point for find function
Cells.Find(What:="location", After:=sht2.cells(1,1), LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(0, -1).Select 'offsets the active cell to the value column in EOM sheet
ActiveCell.Paste 'pastes the activecell value copied from sheet 1
' find function finds the string saved to the location variable , makes it the active cell.
Skip:
Next cellfinder
Upvotes: 0
Views: 218
Reputation: 1529
One thing to note is that in What:="location"
when you put location
in quotations, you're telling your find function to look exclusively for the value "location"
instead of the variable location
which you've set to the value of the ActiveCell
in the loop.
Additionally, I'd avoid using Goto
unless you're exiting a deeply nested loop. You could rewrite your code to avoid using Select
, ActiveCell
, Goto
and correcting your .Find
like:
Dim location As String
Dim rownum As Long
Dim cellfinder As Long
Dim fRng As Range
With Sheets("Sheet1")
rownum = .Range("E" & Rows.Count).End(xlUp).Row
For cellfinder = 1 To rownum
If .Range("E" & cellfinder) <> "" Then
location = .Range("E" & cellfinder)
Set fRng = Sheets("Sheet2").Cells.Find(What:=location, LookIn:=xlFormulas, LookAt:=xlPart) '<-Sheets("Sheet2") can be any sheet you need to perform the .Find against
If Not fRng Is Nothing Then
fRng.Offset(0, -1) = .Range("D" & cellfinder)
Else
'Do something when .Find doesn't find anything
End If
End If
Next cellfinder
End With
Using a With
block allows you to specify the desired object once and utilize it by prefixing methods with a .
so that
With Sheets("Sheet1")
.Range("A1")
End With
Is functionally the same as
Sheets("Sheet1").Range("A1")
Using .Range("E" & Rows.Count).End(xlUp).Row
will return the number of the last used row in Column E, then you can test for blank cells in the loop and not worry about your rownum
count not being long enough when you let it = Range("G2")
.
To circumvent the Goto
statement, using the opposite operator and enclosing the entire subsequent executing code within an If
statement would achieve the same result without encouraging spaghetti code. Instead of testing to see if ActiveCell = ""
and then executing a Goto
when it does, test to see if it doesn't, only executing the following code when it doesn't.
Initializing fRng
as a Range
and setting it equal to the result of your .Find
function will allow you to test the find function to see if it returned anything so you don't error out when you try to do something with fRng
when it's Nothing
.
Upvotes: 1