Pasting values in column next to range

[John]

Each month I download finacial data into a worksheet...the number of
rows varies each month, but the number of columns remains constant. I
have a macro that insert a column to the right of A:A. Now, I need the
cells in the (new) column A to contain a single value (the year) that
extends from row 1 to the end of the last value in column b.

A B C
1 2004 data data
2 2004 data data
3 2004 data data
* data data

The above probably doesn't like aligned, but I'm trying to show how I
want to paste "2004" in each cell of column A all the way down until
the last used cell in column B.

Any help is appreciated!

 

[ksnapp]

here this worked when I tried it.

Sub john()

Dim lr As Long

lr = Cells(Rows.Count, 2).End(xlUp).Row
Range("a1", Cells(lr, 2)).Select

For Each cell In Selection
If Not IsEmpty(cell) Then
cell.Offset(0, -1).Value = "2004"
End If
Next

End Su

 

[John]

Thanks for the code, however, I can't seem to get it to work for me. I
get a cryptic vb 400 error, without any expanation. (I'm using Office
2003). Any suggestions?

TIA

 

[Frank Kabel]

Hi John
problem is what the for next loop also processes column A and tries to
enter a value to the column left to column A (which does not exist).
Try the following:

Sub john()
Dim lr As Long
Dim row_index as long
lr = Cells(Rows.Count, 2).End(xlUp).Row

for row_index = 1 to lr
If Not IsEmpty(cells(row_index,2).value) Then
cells(row_index,1).Value = "2004"
End If
Next
End Sub

 

[Paul Robinson]

Hi
Activate the sheet first if using Select. Also, declare cell as a
range variable if using Option Explicit

Sub john()

Dim lr As Long
Dim cell as Range

Worksheets("TestSheet").Activate 'or your sheet name
lr = Cells(Rows.Count, 2).End(xlUp).Row
ActiveSheet.Range("a1", Cells(lr, 2)).Select

For Each cell In Selection
If Not IsEmpty(cell) Then
cell.Offset(0, -1).Value = "2004"
End If
Next

End Sub

Or avoid Selection:

Sub john()

Dim lr As Long
Dim cell as Range
Dim YearRange as Range

With Worksheets("TestSheet") 'or your sheet name
lr = Cells(.Rows.Count, 2).End(xlUp).Row
Set YearRange = .Range(.Range("a1"), .Cells(lr, 2))

For Each cell In YearRange
If Not IsEmpty(cell) Then
cell.Offset(0, -1).Value = "2004"
End If
Next
end with

Set YearRange = Nothing
End Sub


regards
Paul

 

[John]

Thanks for the correction...the code now works for me!

John