VBA: How can i select the cell in a row which matches a variable's value?

Question

I have 2 sheets. Sheet1 has 2 rows: column names and values. Sheet 2 is a master sheet with all the possible column names in. I need to copy the values from sheet 1 into their appropriate column.

I think i can do this via a match function, and so far i have this:

Sub dynamic_paste()

Dim Columnname As String
Dim inputvalue As String
Dim starter As Integer
Dim i As Integer
starter = 0

For i = 1 To 4
'replace 4 with rangeused.rows.count?

Sheets("sheet1").Select
Range("a1").Select
ActiveCell.Offset(0, starter).Select
Columnname = ActiveCell
'sets columnname variable

ActiveCell.Offset(1, 0).Select
inputvalue = ActiveCell
'sets inputname variable

Sheets("sheet2").Select

'**Cells(0, WorksheetFunction.Match(Columnname, Rows(1), 0)).Select**

Range("a1").Offset(1, starter).Value = inputvalue
'inputs variable in the next cell along

starter = starter + 1
Next

End Sub

I need to find out how to use my columnname variable as the matching value, and then offset down to the first row that is empty - then change the value of that cell to the variable called inputvalue.

For extra points: I need to make sure the code doesnt break if they dont find a matching value, and if possible put any values that dont match into the end of the row?

`Cells(0,` would always throw an error. The first row is number 1, thus `Cells(1,1)` is the minimum). — Vityata, Jun 20 '18 at 12:38
okay but even if i change that i get a object-defined or application-defined error. — Charlie Felix, Jun 20 '18 at 12:44
Of course. But you are one step closer. Try this - `Sheets("sheet2").Cells(1, WorksheetFunction.Match(Columnname, Sheets("sheet2").Rows(1), 0)).Select` and see where you are getting the error. — Vityata, Jun 20 '18 at 12:45
The code runs up until sheets(sheet2).select tho, so i know the match function is the problem — Charlie Felix, Jun 20 '18 at 13:12

JvdV · Accepted Answer · 2018-06-22T11:30:49.853

0

What about this:

Dim LR As Long, X As Long, LC As Long, COL As Long
Dim RNG As Range, CL As Range

Option Explicit

Sub Test()

LR = Sheets(2).Cells.SpecialCells(xlCellTypeLastCell).Row 'Get last used row in your sheet
LC = Sheets(2).Cells(1, Sheets(2).Columns.Count).End(xlToLeft).Column 'Get last used column in your sheet
Set RNG = Sheets(2).Range(Sheets(2).Cells(1, 1), Sheets(2).Cells(1, LC))

'Loop through all the columns on your sheet with values
For X = 1 To Sheets(1).Cells(1, Sheets(1).Columns.Count).End(xlToLeft).Column
    Set CL = RNG.Find(Sheets(1).Cells(1, X).Value, lookat:=xlWhole)
    If Not CL Is Nothing Then
        COL = CL.Column
        Sheets(2).Cells(LR + 1, COL).Value = Sheets(1).Cells(2, X).Value 'Get the value on LR offset by 1
    Else
        Sheets(2).Cells(1, Sheets(2).Cells(1, Sheets(2).Columns.Count).End(xlToLeft).Column).Value = Sheets(1).Cells(1, X).Value
        Sheets(2).Cells(LR + 1, Sheets(2).Cells(1, Sheets(2).Columns.Count).End(xlToLeft).Column).Value = Sheets(1).Cells(2, X).Value
    End If
Next X

End Sub

This way you will avoid using select. Which is very recommandable!

edited Jun 22 '18 at 11:30

answered Jun 20 '18 at 14:04

JvdV

70,606
8
39
70

Hi, that works perfectly except for one thing! It puts the new values at the bottom of each column. Sometimes my column will be missing values. Therefore the values will be put out of their respective row. Is there a way to fix this? – Charlie Felix Jun 20 '18 at 15:00
@CharlieFelix, I have updated the answer. I made it so it won't error out on a variable that won't be in your master data. It's not necessarily shorter anymore :) – JvdV Jun 20 '18 at 15:58
@charlieFelix, no problem. See where it says ‘If not CL is nothing then ..... end if’? You can make an else in between that will handle with the missing values (your bonus question). Good luck – JvdV Jun 21 '18 at 06:19
so if not cl is nothing then XYZ else copy cell value to the end of the row? – Charlie Felix Jun 21 '18 at 07:50

score 0 · Answer 2 · answered Jun 20 '18 at 14:07

This is Sheet1:

This is Sheet2:

This is the code:

Option Explicit

Sub DynamicPaste()

    Dim col As Long
    Dim wks1 As Worksheet: Set wks1 = Worksheets(1)
    Dim wks2 As Worksheet: Set wks2 = Worksheets(2)

    For col = 1 To 3
        Dim currentRow As Long
        currentRow = WorksheetFunction.Match(wks2.Cells(1, col), wks1.Columns(1))
        wks2.Cells(2, col) = wks1.Cells(currentRow, 2)
    Next col

End Sub

This is Sheet2 after the code:

This is a must-read - How to avoid using Select in Excel VBA

Hi, This nearly works but not quite. All my values are strings (not sure if that changes anything) but sheet2 row2 has 3 values in which are all the same value. — Charlie Felix, Jun 20 '18 at 14:45

VBA: How can i select the cell in a row which matches a variable's value?

2 Answers2