Claudio1978
07-14-2021, 11:04 AM
With the following code:
Sub IndexMatchFunction()
Dim destinationWs As Worksheet, dataWs As Worksheet
Dim destinationLastRow As Long, dataLastRow As Long, x As Long
Dim IndexRng As Range, MatchRng As Range
Set destinationWs = ThisWorkbook.Worksheets("Weekly")
Set dataWs = ThisWorkbook.Worksheets("Market Data")
destinationLastRow = destinationWs.Range("A" & Rows.Count).End(xlUp).Row
dataLastRow = dataWs.Range("A" & Rows.Count).End(xlUp).Row
'The column where data needs to be pulled from
Set IndexRng = dataWs.Range("P3:P" & dataLastRow)
'Matching column in the sheet containing the data to be pulled
Set MatchRng = dataWs.Range("A3:A" & dataLastRow)
For x = 2 To destinationLastRow
On Error Resume Next
'1st line: in which column of the sheets do you need to put data?
'2nd line: the matching column of the sheet where you need to put data
a = Application.Match(destinationWs.Range("A" & x).Value, MatchRng, 0)
Nights = Application.Index(IndexRng, a)
If (IsError(a) Or IsEmpty(Nights)) Or (IsError(a) And IsEmpty(Nights)) Then
destinationWs.Range("C" & x).Value = 0
Else
destinationWs.Range("C" & x).Value = Nights
End If
On Error GoTo 0
Next x
End Sub
I am filling a column with data pulled from another sheet of the same workbook. it is an index match function.
As you can see, column C of the Weekly sheet is filled with data coming from another sheet named Market Data.
I will update market data on a weekly basis and what I would like to do is to make sure that previous week data is not overwritten by the new data. In order to accomplish that, I would like to modify my code so that when I update the market data, the system will add data in the weekly sheet in column D. The week after the data will need to be filled in column E and so on.
How can I accomplish that?
Thank you
Sub IndexMatchFunction()
Dim destinationWs As Worksheet, dataWs As Worksheet
Dim destinationLastRow As Long, dataLastRow As Long, x As Long
Dim IndexRng As Range, MatchRng As Range
Set destinationWs = ThisWorkbook.Worksheets("Weekly")
Set dataWs = ThisWorkbook.Worksheets("Market Data")
destinationLastRow = destinationWs.Range("A" & Rows.Count).End(xlUp).Row
dataLastRow = dataWs.Range("A" & Rows.Count).End(xlUp).Row
'The column where data needs to be pulled from
Set IndexRng = dataWs.Range("P3:P" & dataLastRow)
'Matching column in the sheet containing the data to be pulled
Set MatchRng = dataWs.Range("A3:A" & dataLastRow)
For x = 2 To destinationLastRow
On Error Resume Next
'1st line: in which column of the sheets do you need to put data?
'2nd line: the matching column of the sheet where you need to put data
a = Application.Match(destinationWs.Range("A" & x).Value, MatchRng, 0)
Nights = Application.Index(IndexRng, a)
If (IsError(a) Or IsEmpty(Nights)) Or (IsError(a) And IsEmpty(Nights)) Then
destinationWs.Range("C" & x).Value = 0
Else
destinationWs.Range("C" & x).Value = Nights
End If
On Error GoTo 0
Next x
End Sub
I am filling a column with data pulled from another sheet of the same workbook. it is an index match function.
As you can see, column C of the Weekly sheet is filled with data coming from another sheet named Market Data.
I will update market data on a weekly basis and what I would like to do is to make sure that previous week data is not overwritten by the new data. In order to accomplish that, I would like to modify my code so that when I update the market data, the system will add data in the weekly sheet in column D. The week after the data will need to be filled in column E and so on.
How can I accomplish that?
Thank you