CODEWITHSUNDEEP
excelvbaloopsrange
VBAWARD
VBAWARD

Reputation: 71

How to get VBA code to check if cell value matches column heading and return value from another cell in the same row?

I've searched all over and can't seem to get this piece working.

I have 2 worksheets.

Sheet 1 contains the quiz question numbers and then the score for each corresponding q#.

Sheet 2 contains each q# as column headings and I'd like for the VBA code to grab the score from the q# that corresponds to the right heading.

For ex:

Sheet 1

Question#      Score 
1               100
2                90
3                75
4                95

Sheet 2

Q1    Q2    Q3   Q4 
100   90    75   95

The column headings in Sheet 2 can be changed to simply say "1", "2", etc so there can be an exact match if that makes it easier.

I found and played with this code but it copies the entire column if it finds a match which doesnt work:

Sub CopyDataDynamically()
Dim lr As Long, num As Long
Dim rng As Range
lr = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range("a2")
num = Range("b2").Value
If num = 1 Then
    Range("B2:B" & lr).Copy
    rng.Offset(0, num - 1).PasteSpecial xlPasteValues
End If
End Sub

Thanks in advance!

Upvotes: 0

Views: 1394

Answers (1)

alowflyingpig
alowflyingpig

Reputation: 738

I always find it good practice to Dim your Workbook and Worksheet. If you decide to expand you're already set.

The following code will essentially transpose the data set, no matter how many questions.

Sub test()


Dim wbk As Workbook
Dim wks_1 As Worksheet
Dim wks_2 As Worksheet

    Set wbk = ThisWorkbook
    Set wks_1 = wbk.Sheets("Sheet1")
    Set wks_2 = wbk.Sheets("Sheet2")

    LastRow = wks_1.Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To LastRow
        wks_1.Cells(i, 1).Copy wks_2.Cells(1, i)
        wks_1.Cells(i, 2).Copy wks_2.Cells(2, i)
    Next i


End Sub

It will copy the headers also.

Firstly I want to know where the last row is, hence the LastRow code. Then we simply loop through all rows and transpose the data set.

The reference .Cells in line wks_1.Cells(i, 1).Copy wks_2.Cells(1, i) always references Row then Col.

Let me know how you go.

Upvotes: 1

Related Questions