chasing ranges that move

[rhk001]

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

[mdmackillop]

Are your columns adjoining or might they be separated? If the latter, does your range include all columns between them or just the two columns?

[rhk001]

Hi thanks for the reply.

The columns may be apart, the ranges I am creating is looking for start, then looking for the last entry in that column, (naming the range) and doing the same thing for Finish, I am not sure if this is the right approach to the problem.

[mdmackillop]

Option Explicit
Sub MoveStartToEnd02() 'Moves start date to the finish date column wherever the finish date is missing
Dim cell As Range
Dim S As Long
Dim F As Long
S = Rows(1).Find("Start").Column ' Finds Start
F = Rows(1).Find("Finish").Column ' Finds Finish


For Each cell In Rng(S).Offset(, F - S)
If cell.Value = "" Then
cell.Value = cell.Offset(, S - F)
End If
Next
End Sub

Function Rng(Col) As Range
Set Rng = Range(Cells(2, Col), Cells(Rows.Count, Col).End(xlUp))
End Function

[mdmackillop]

Adjusting your own code, Start should be used to determine the length of the ranges "Finish" may have no values, so looping it won't work.

Sub MoveStartToEnd3() '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

Set MySRange = Range(S.Offset(1), Cells(Rows.Count, S.Column).End(xlUp)) ' Defines the range of Start
Set MyFRange = F.Offset(1).Resize(MySRange.Cells.Count)
For Each cell In MyFRange ' (Myfrange & LastRow)
x = x + 1
If cell = "" Then
cell.Value = MySRange(x) ' (Sheet1.Range("Mysrange" & x).Value)
End If
Next
End Sub

[mikerickson]

The code above will help you find headers on a sheet, but when I am setting up a spreadsheet, I prefer to use named ranges as a path to adjustablility.
I'll make each header cell its own named range and refer to those in my code.
Eg. to add a new Full Name
With Range("FullName").EntireColumn
Cells(.Rows.Count,1).End(xlup).Offset(1,0).Value = "New Name"
End With
The thing I like about named ranges is that insert/delete/cut/paste don't matter, Excel adjusts things as it goes.

[rhk001]

Thanks very much for your help with this guys, I will have play around with all of the options and post my final answer for critique. :-)