lw22
06-29-2009, 02:34 PM
I am not sure if this is the correct forum or even site but I'm looking for a little help with an Excel VBA macro. I have been reading up on VBA coding and found that (for the most part) you should avoid using loops. Also, you should use a for each loop rather than a regular for loop when possible. This is where my problem lies.
I have a program that pulls data from a database with a custom Excel function. Since it takes awhile for the function to run after the data populates I wrote a macro to copy the data and paste it to a sheet where it can be logged. First in Excel I renamed the automatic input sheet to sInput and the data logging sheet to sFill. When the program runs it checks the date the data was gathered and returns the corresponding row in sFill. Then it loops through the names in sInput to the names in sFill. If the names are the same it copies the data.
To loop through the names I am using for loops. Is there a way to range the cells the names are in, do the comparison, and return the row where the names are equal? The code is below for your reference. If you need the actual file I can provide that as well.
Option Explicit
Sub MoveToFill()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Moves data from the automatic input sheet to the data logging sheet
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim i As Long, rInput As String, rFill As String, rDates As String, j As Long, lngDate As Long
Dim strInput As String, strFill As String, lngInput As Long, lngFill As Long, lngGPM As Long
Dim m As Long
'Set ranges for the tank comparison - not set up yet
' rInput = "D10:D14"
' rFill = "C13:S13"
' rDates = "A6:A9"
'Find date the data is for
For m = 6 To 9
If sFill.Cells(m, 1) = sInput.Cells(4, 5) Then
lngDate = m
Exit For
Else
MsgBox "Warning - Date entered is invalid"
Exit Sub
End If
Next m
'Loop through to put data at matching Fill data
For i = 10 To 14
'Only copies data if an x is placed in the adjacent cell
If sInput.Cells(i, 3) = "x" Then
'Loop through Fill names
For j = 3 To 19 Step 4
'If names are the same it copies the auto data
If sInput.Cells(i, 4) = sFill.Cells(13, j) Then
sFill.Cells(lngDate, j) = sInput.Cells(i, 5)
sFill.Cells(lngDate, j + 1) = sInput.Cells(i, 6)
'Adds Name3's rate since it is calculated seperate
If sInput.Cells(i, 4) = "Name3" Then
sFill.Cells(lngDate, j + 3) = sInput.Cells(i, 7)
End If
Exit For
End If
Next j
End If
Next i
sFill.Activate
End Sub
I do have another question... This code is placed in ThisWorkbook. On the sInput sheet I have a button that I use to call the ThisWorkbook.MoveToFill() sub. I was playing around with changing the name property of all of the sheets and I renamed the name of ThisWorkbook to wLog. When I try to run wLog.MoveToFill() the program doesn't work. Any thoughts on why this is happening?
I have a program that pulls data from a database with a custom Excel function. Since it takes awhile for the function to run after the data populates I wrote a macro to copy the data and paste it to a sheet where it can be logged. First in Excel I renamed the automatic input sheet to sInput and the data logging sheet to sFill. When the program runs it checks the date the data was gathered and returns the corresponding row in sFill. Then it loops through the names in sInput to the names in sFill. If the names are the same it copies the data.
To loop through the names I am using for loops. Is there a way to range the cells the names are in, do the comparison, and return the row where the names are equal? The code is below for your reference. If you need the actual file I can provide that as well.
Option Explicit
Sub MoveToFill()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'Moves data from the automatic input sheet to the data logging sheet
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Dim i As Long, rInput As String, rFill As String, rDates As String, j As Long, lngDate As Long
Dim strInput As String, strFill As String, lngInput As Long, lngFill As Long, lngGPM As Long
Dim m As Long
'Set ranges for the tank comparison - not set up yet
' rInput = "D10:D14"
' rFill = "C13:S13"
' rDates = "A6:A9"
'Find date the data is for
For m = 6 To 9
If sFill.Cells(m, 1) = sInput.Cells(4, 5) Then
lngDate = m
Exit For
Else
MsgBox "Warning - Date entered is invalid"
Exit Sub
End If
Next m
'Loop through to put data at matching Fill data
For i = 10 To 14
'Only copies data if an x is placed in the adjacent cell
If sInput.Cells(i, 3) = "x" Then
'Loop through Fill names
For j = 3 To 19 Step 4
'If names are the same it copies the auto data
If sInput.Cells(i, 4) = sFill.Cells(13, j) Then
sFill.Cells(lngDate, j) = sInput.Cells(i, 5)
sFill.Cells(lngDate, j + 1) = sInput.Cells(i, 6)
'Adds Name3's rate since it is calculated seperate
If sInput.Cells(i, 4) = "Name3" Then
sFill.Cells(lngDate, j + 3) = sInput.Cells(i, 7)
End If
Exit For
End If
Next j
End If
Next i
sFill.Activate
End Sub
I do have another question... This code is placed in ThisWorkbook. On the sInput sheet I have a button that I use to call the ThisWorkbook.MoveToFill() sub. I was playing around with changing the name property of all of the sheets and I renamed the name of ThisWorkbook to wLog. When I try to run wLog.MoveToFill() the program doesn't work. Any thoughts on why this is happening?