Forum Discussion
Solved
VBA to move cell values to a cell address in each row of a table
Hi, I have a list on sheet "New Template" of values in range L9:L32 (New Date) with cell addresses (eg, $D$14) in the next column range M9:M32 (Cell Ref) that I need the values coping to on a sheet ...
Thanks. The cause of the problem was that there are other data below the update table.
This version should work:
Sub MoveDates() Dim ws As Worksheet Dim wt As Worksheet Dim s As Long Dim m As Long Application.ScreenUpdating = False Set ws = Worksheets("New Template") Set wt = Worksheets("Dates") m = ws.Range("L31").End(xlUp).Row For s = 9 To m wt.Range(ws.Range("M" & s).Value).Value = ws.Range("L" & s).Value Next s ws.Range("L9:L" & m).ClearContents Application.ScreenUpdating = True End Sub
A slightly different version:
Sub MoveDates()
Dim ws As Worksheet
Dim wt As Worksheet
Dim s As Long
Dim m As Long
Application.ScreenUpdating = False
Set ws = Worksheets("New Template")
Set wt = Worksheets("Dates")
m = ws.Range("L" & ws.Rows.Count).End(xlUp).Row
For s = 9 To m
If ws.Range("L" & s).Value <> "" Then
wt.Range(ws.Range("M" & s).Value).Value = ws.Range("L" & s).Value
End If
Next s
ws.Range("L9:L" & m).ClearContents
Application.ScreenUpdating = True
End Sub
Thank you so much, I ran this and got a Type 13 mismatch :
If ws.Range("L" & s).Value <> "" ThenDoes column L contain error values?
- Just a note, column M are formula that returns the cell address, would that be causing an issue?
No, that shouldn't be a problem.
Could you attach a sample workbook (without sensitive data), or if that is not possible, make it available through OneDrive, Google Drive, Dropbox or similar? Alternatively, you can attach it to a private message to me. Thanks in advance.
- No, just dates or blank.