'How to take two cell reference from another sheet in vba
I am not able to take the reference from column AB and AC given in sheet ("UPDATER") to sheet("Historical_vol"), can anyone please confirm what am i doing wrong here ?
Sub historical_vol()
Application.ScreenUpdating = True
'This will help to watch the status bar update
Application.Calculation = xlCalculationManual
Dim wb As Workbook, uPd As Worksheet, hV As Worksheet
Dim lr As Long, cl As Range
Set wb = ThisWorkbook
Set uPd = wb.Sheets("UPDATER")
Set hV = wb.Sheets("Historical_vol")
uPd.Activate
uPd.Range("AD4:AG4", Range("AD4").End(xlDown)).Clear
lr = uPd.Cells(Rows.Count, "AB").End(xlUp).Row
i = 0
For i = 4 To lr
hV.Range("A9:B9").Value = uPd.Range("AB" & i & ":AC" & i).Value
hV.Calculate
DoEvents
uPd.Range("AD" & i & ":AG" & i).Value = hV.Range("C9:F9").Value
Application.StatusBar = i - 3 & " / " & lr - 3
'View on status bar number of records completed out of total records (lr-3)
Next i
Application.Calculation = xlCalculationAutomatic
End Sub
UPDATER SHEET
HISTORICAL_VOL SHEET
Solution 1:[1]
Try changing the line
For Each cl In uPd.Range("AB4:AC4" & lr)
to
For Each cl In uPd.Range("AB4:AC" & lr).Rows
and the line
cl.Offset(0, 2).Resize(1, 4).Value = hV.Range("C9:F9").Value
to
cl.Offset(0, 2).Resize(1, 3).Value = hV.Range("C9:F9").Value
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 |