blowing my mind

[dialup]

Hello all

I am not very knowledgable in vba and have been desperatly trying to ammend macros to do what i need with no success.
i have a sheet with lots of columns
i need a macro to copy what you see in column AS into column AT from the row2 to the last populated row on the sheet.
column AS is a date formated to show the day only - i need AT to show the word not the formula so vba copying the text in AS to AT works - i just cant get it to find the last populated cell in column A and run from row 2 to that row.
Can anyone please help?

 

[lenze]

Sub Fanugi()
Dim LR As Integer
Dim cl As Range
LR = Range("$A" & Rows.Count).Row
For Each cl In Range(Cells(2, 1), Cells(LR, 1))
     'Your code
Next cl
    
End Sub

lenze

 

[dialup]

thanks for the quick response

I tried this code but get Run-time error 6 : overflow

I am using 2003

 

[Jonmo1]

Should probably use Long instead of Integer for LR

Dim LR As Long


I would guess your data is pretty large..Integer is limited to 32,767
So if you have 32,768 or more rows, then it would overflow..

 

[Jonmo1]

Also

This

LR = Range("$A" & Rows.Count).Row

was probably meant to be

LR = Range("$A" & Rows.Count).End(xlUp).Row

 

[dialup]

ok so my code looks like

Sub Fanugi()
Application.ScreenUpdating = False
Dim LR As Long

Dim cl As Range
With Worksheets("Master")
LR = Range("$A" & Rows.count).End(xlUp).Row
For Each cl In Range(Cells(2, 1), Cells(LR, 1))
Worksheets("Master").Range("at" & Rows.count) = Worksheets("Master").Range("as" & Rows.count).Text
Next cl
    End With
End Sub

when i run the macro the egg timer comes up but column AT is still empty

 

[Jonmo1]

Try

Sub Fanugi()
Application.ScreenUpdating = False
Dim LR As Long
Dim cl As Range

With Worksheets("Master")
    LR = .Range("AS" & Rows.count).End(xlUp).Row
    For Each cl In .Range(.Cells(2, 1), .Cells(LR, 1))
        cl.Offset(0,1).Value = cl.Text
    Next cl
End With
Application.ScreenUpdating = True
End Sub

 

[dialup]

still no joy - column AS is populated with a date formatted to show the day - i have even replaced cl.text with "bob" just to see if the column was populated with the word but it wasnt??

so i created a new spreadsheet, filled column a with hello, changed the column reference in the macro to A, ran the macro and it works fine. so the issue must be with my spreadsheet.

 

[Jonmo1]

OK, macro was previously written to read dates from column A, not column AS

For Each cl in .Range(.Cells(2,1),.Cells(LR,1))

That should be

For Each cl in .Range(.Cells(2,"AS"),.Cells(LR,"AS"))