rhk001
04-23-2009, 09:41 AM
Hi I am after a bit of advise on the best way to code up my VBA to handel data that gets imported.
I have many fields that come in, but the column position varies as does the row number with every import. The column names come in at Row 1, and the names are always consistant.
When putting code together is seems fairly easy to point to a range when you know where it is, as seen in the first example (this was with the help of Cregantur :hi: . (The columns I was after were C & D).
In the second example, I have tried (Badly) to create a range by looking for the name Start & the name finish, but I am just not getting it. This example does not work.
Does anyone have a good way to explain it to someone who is struggeling to understand? Is using a range the best way to approach this?
Example 1
Sub MoveStartToEnd() 'Moves start date to the finish date column wherever the finish date is missing
Dim cell As Range
Dim x As Integer
Dim LastRow As Long
LastRow = Sheet1.Range("A65536").End(xlUp).Row
x = 2
For Each cell In Range("D2:D" & LastRow)
If cell.Value = "" Then
cell.Value = Sheet1.Range("C" & x).Value
End If
x = x + 1
Next
MsgBox "Date adjustments have been completed."
End Sub
Example 2
Sub MoveStartToEnd02() 'Moves start date to the finish date column wherever the finish date is missing
Dim cell As Range
Dim S As Range
Dim F As Range
Dim MySRange As Range
Dim MyFRange As Range
Set S = Rows(1).Find("Start") ' Finds Start
Set F = Rows(1).Find("Finish") ' Finds Finish
Dim x As Integer
Dim LastRow As Long
LastRow = Sheet1.Range("A65536").End(xlUp).Row
x = 2
Set MySRange = Range(S.Offset(1), Cells(Rows.Count, S.Column).End(xlUp)) ' Defines the range of Start
Set MyFRange = Range(F.Offset(1), Cells(Rows.Count, F.Column).End(xlUp)) ' Defines the range of Finish
For Each cell In MyFRange ' (Myfrange & LastRow)
If cell.Value = "" Then
cell.Value = MySRange ' (Sheet1.Range("Mysrange" & x).Value)
End If
x = x + 1
Next
'MsgBox "Date adjustments have been completed."
End Sub
I have many fields that come in, but the column position varies as does the row number with every import. The column names come in at Row 1, and the names are always consistant.
When putting code together is seems fairly easy to point to a range when you know where it is, as seen in the first example (this was with the help of Cregantur :hi: . (The columns I was after were C & D).
In the second example, I have tried (Badly) to create a range by looking for the name Start & the name finish, but I am just not getting it. This example does not work.
Does anyone have a good way to explain it to someone who is struggeling to understand? Is using a range the best way to approach this?
Example 1
Sub MoveStartToEnd() 'Moves start date to the finish date column wherever the finish date is missing
Dim cell As Range
Dim x As Integer
Dim LastRow As Long
LastRow = Sheet1.Range("A65536").End(xlUp).Row
x = 2
For Each cell In Range("D2:D" & LastRow)
If cell.Value = "" Then
cell.Value = Sheet1.Range("C" & x).Value
End If
x = x + 1
Next
MsgBox "Date adjustments have been completed."
End Sub
Example 2
Sub MoveStartToEnd02() 'Moves start date to the finish date column wherever the finish date is missing
Dim cell As Range
Dim S As Range
Dim F As Range
Dim MySRange As Range
Dim MyFRange As Range
Set S = Rows(1).Find("Start") ' Finds Start
Set F = Rows(1).Find("Finish") ' Finds Finish
Dim x As Integer
Dim LastRow As Long
LastRow = Sheet1.Range("A65536").End(xlUp).Row
x = 2
Set MySRange = Range(S.Offset(1), Cells(Rows.Count, S.Column).End(xlUp)) ' Defines the range of Start
Set MyFRange = Range(F.Offset(1), Cells(Rows.Count, F.Column).End(xlUp)) ' Defines the range of Finish
For Each cell In MyFRange ' (Myfrange & LastRow)
If cell.Value = "" Then
cell.Value = MySRange ' (Sheet1.Range("Mysrange" & x).Value)
End If
x = x + 1
Next
'MsgBox "Date adjustments have been completed."
End Sub