hlookup in VBA

[shaunkaz]

Hi I have a drop down box in "A2" which is changed by month eg. (Apr- 16), in cells "A3:A8" this pulls through all sales data for that month.

I copy that data from cells "A3:A8" and paste values ONLY in the corresponding month in a table "C2: F8".

I need a macro that copies the figures in column "A" and pastes values only in table (C2:F8) for the corresponding month which is chosen in cell "A2"

I will then assign a macro button for this.

Apr-16		Jan-16	Feb-16	Mar-16	Apr-16
2567		10	16	1544	2567
100		1	887	5	100
266		55	0	3	266
666		489	0	8	666
948		34	88	99	948
348		5	67	1	348

<tbody>
</tbody>

 

[RCBricker]

Hi I have a drop down box in "A2" which is changed by month eg. (Apr- 16), in cells "A3:A8" this pulls through all sales data for that month.

I copy that data from cells "A3:A8" and paste values ONLY in the corresponding month in a table "C2: F8".

I need a macro that copies the figures in column "A" and pastes values only in table (C2:F8) for the corresponding month which is chosen in cell "A2"

I will then assign a macro button for this.

Apr-16		Jan-16	Feb-16	Mar-16	Apr-16
2567		10	16	1544	2567
100		1	887	5	100
266		55	0	3	266
666		489	0	8	666
948		34	88	99	948
348		5	67	1	348

<tbody>
</tbody>

since you are adding this to a button, this should work

Sub Shaunkaz()
Dim lngrow As Long, lngcol As Long
Dim rng As Range, rngHEAD As Range, cell As Range
Dim strCELL As String
Dim intCELL As Integer
Dim ws As Worksheet

Set ws = ActiveSheet
Set cell = ws.Range("A2")
With ws
    lngrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
    lngcol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
    Set rngHEAD = ws.Range(ws.Cells(2, 1), ws.Cells(2, lngcol))
    Set rng = ws.Range(ws.Cells(3, 1), ws.Cells(lngrow, 1))
    intCELL = rngHEAD.Find(cell.Value).Column
    rng.Copy
    ws.Cells(3, intCELL).PasteSpecial xlPasteValues
End With

End Sub

 

[shaunkaz]

Hi I get an error "Object variable or With block variable not set"

 

[RCBricker]

on what line? the code worked for me using the data you provided.

 

[shaunkaz]

Hi Sorry yes it does work, but not on my original sheet..

However is it possible to define a range to copy ie.. A3:A8 as the code above copys all data in column "A". Its just that I have other data below and this is also being copied

Also Where as on the table above, the datatable as shown where it is to be pasted is column "C" (2 columns away), where as on my original sheet it is coulmn D (3 columns away).

Can you adjust the code to compensate

Thanks
Shaun

 

[RCBricker]

Hi Sorry yes it does work, but not on my original sheet..

However is it possible to define a range to copy ie.. A3:A8 as the code above copys all data in column "A". Its just that I have other data below and this is also being copied

Also Where as on the table above, the datatable as shown where it is to be pasted is column "C" (2 columns away), where as on my original sheet it is coulmn D (3 columns away).

Can you adjust the code to compensate

Thanks
Shaun

Sorry was out of the office for the holiday.

Try this

Sub Shaunkaz_v2()
Dim lngrow As Long, lngcol As Long
Dim rng As Range, rngHEAD As Range, cell As Range
Dim intCELL As Integer
Dim ws As Worksheet

    Set ws = ActiveSheet
    Set cell = ws.Range("A2")
    With ws
        lngrow = ws.Range("A2").End(xlDown).Row
        lngcol = ws.Cells(2, ws.Columns.Count).End(xlToLeft).Column
        Set rngHEAD = ws.Range(ws.Cells(2, 1), ws.Cells(2, lngcol))
        Set rng = ws.Range(ws.Cells(3, 1), ws.Cells(lngrow, 1))
        intCELL = rngHEAD.Find(cell.Value).Column
        rng.Copy
        ws.Cells(4, intCELL).PasteSpecial xlPasteValues
    End With
End Sub