Sleeper: How to dynamically add data into a new column to the right

[Claudio1978]

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

[jolivanes]

You have figured out how to find the last Row.
So the next free cell is the cell below the last used cell (lastcell.Offset(1))
Similar for the Column
Last used Cell in Row1

lc = Cells(1, Columns.Count).End(xlToLeft).Column
So the next free Column based on the top row would be

Cells(1, Columns.Count).End(xlToLeft).Column + 1
The 1 in above examples is fir the first Row. Change accordingly
You can also use

Range("XFD1").End(xlToLeft).Column + 1 '(the 1 in XFD1 again is for the first row)
If you have staggering row lengths and you need the first free column, use

First free column = Cells.Find("*", , , , xlByColumns, xlPrevious).Column.Offset(, 1)


A quick example would be

Dim sh1 As Worksheet, sh2 As Worksheet
Set sh1 = Sheets("Data")
Set sh2 = Sheets("Destination")
sh1.Range("D1:D" & sh1.Cells(Rows.Count, 4).End(xlUp).Row).Copy sh2.Cells(1, sh2.Cells.Find("*", , , , xlByColumns, xlPrevious).Column).Offset(, 1)

[Claudio1978]

Not very clear sorry about how to modify the below code:


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

in order to reach the desired results.

[Claudio1978]

This is what I did at the moment but the error is Method "Range of Object" _Worksheet failed.




Sub IndexMatchFunction()


Dim destinationWs As Worksheet, dataWs As Worksheet
Dim destinationLastRow As Long, dataLastRow As Long, x As Long, lCol As Long
Dim IndexRng As Range, IndexRng2 As Range, MatchRng As Range


Set destinationWs = ThisWorkbook.Worksheets("Destination")
Set dataWs = ThisWorkbook.Worksheets("Population")


destinationLastRow = destinationWs.Range("A" & Rows.Count).End(xlUp).Row
dataLastRow = dataWs.Range("A" & Rows.Count).End(xlUp).Row


Set IndexRng = dataWs.Range("B2:B" & dataLastRow)
Set MatchRng = dataWs.Range("A2:A" & dataLastRow)

lCol = Range(Range("A2"), Cells(Rows.Count, Columns.Count)).Find(What:="*", _
LookIn:=xlValues, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column + 1

For x = 2 To destinationLastRow
On Error Resume Next


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("lCol" & x) = 0


Else


destinationWs.Range("lCol" & x) = Nights


End If
On Error GoTo 0
Next x
End Sub




I can I use the lCol value in my destinationWs.Range("lCol" & x) = Nights?

Thank you

[jolivanes]

Did you try the last snippet, changed to proper references, in Post #2?

[abel1303]

Replace n with the last row of your data, YourDataArray with the actual array or value you want to add, and optionally, update the header text as needed.