Nrthugu

I have a sheet "2018" and "2019" which i created previously. 2019 only differs from 2018 in that it may have some rows added and/or some deleted.
Form Cell "A3" and downwards i have skills listed and a "X" in the columns after if the person the column belongs to has this skill.

Now i need to fill the columns of 2019 with known X with formula below, first a bit of context code for the range selection part:

Dim rng As Range

Dim rngbegin As Range

Dim rngend As Range

Dim newrng As Range



Sheets("2018").Activate



   Set rng = Application.InputBox '...and rest of the code



rng.Copy

Sheets("2019").Range("B:B").Insert Shift:=xlToRight



Sheets(2019).Activate

Set rngbegin = rng.Cells(3, 1)

Set rngend = rng.Cells(3000, rng.Columns.Count)

Set newrng = Range(rngbegin.Address & ":" & rngend.Address)

newrng.ClearContents 'To clear everything in the difined range but the headder rows

Here is a formula i could use if the columns wouldn't be varying.

Range("B3").Select

ActiveCell.Formula = "=IFERROR(LOOKUP(2,1/($A3='2018'!$A$3:D$5000),'2018'!$B$3:$B$5000),"")"

    Range("B2").AutoFill Destination:=Range("B2:B" & Range("A" & Rows.Count).End(xlUp).Row)

The Formula works but i have the following problems:

1 - Required) I can't hardcode the formula for every column because the number of colums may change. (I store the number of columns as range var selected from the user via application.inputbox - that's how i inserted the colums in the new 2019 sheet)

2 - optional) I hardcoded the rows to a much higher number than are used because i didn't thnk of counting Column A and then use the range.count.Address(?) as end of the search vector. Just came into my mind lol

You will probably need to tweak a few addresses. I left much of your code unchanged so you can easily adapt what I have came up with for your purposes.

Sub Whatever()



With Sheets("2018")



    ' Get the address of the old range, not used later in the macro

    iRows = .Cells(Rows.Count, 1).End(xlUp).Row

    iCols = .Cells(3, Columns.Count).End(xlToLeft).Column

    Set rngOld = Range(.Cells(3, 2), .Cells(iRows, iCols))



End With



With Sheets("2019")



    ' Get the address of the new range

    iRows = .Cells(Rows.Count, 1).End(xlUp).Row

    iCols = .Cells(2, Columns.Count).End(xlToLeft).Column



    Set rngNew = Range(.Cells(3, 2), .Cells(iRows, iCols))



    'Clear the new range

    rngNew.Clear



    ' Populate the formula

    ' Not very elegant, VBA solution would probably look nicer

    .Range("B3").Formula = "=IFERROR(if(LOOKUP(2,1/('2018'!$A$3:$A$" & iRows & " =$A3),'2018'!B$3:B$" & iRows & ")=""X"",""X"",""""),"""")"



    'Fill the formula

    Set rngTemp = .Range(.Cells(3, 2), .Cells(3, iCols))

    rngTemp.FillRight

    Set rngTemp = .Range(.Cells(3, 2), .Cells(iRows, iCols))

    rngTemp.FillDown



End With



End Sub