Seeking assistance with copy/paste based on cell criteria

[auto.pilot]

I searched but could not find anything similar to this problem.

Using Excel 2010, my workbook has two sheets. The first sheet, 'ClientHoldings' includes a list of identifiers in column F, starting in F7, then down a couple thousand rows. I need to populate via copy paste, the fields (currently blank) in columns BA:BI. The data needed to fill these fields is on my second sheet called 'Criteria'. The list of identifiers is in column C, starting in C6, then down a couple hundred rows. The data I need to copy, then paste is in columns D:L, starting in row 6. Each row needs to be populated separately, since the two lists vary in size and sort order. For example, the identifier 453A7KB6 is the first one listed on 'ClientHoldings', but it's not the first one on the 'Criteria' sheet. I need to copy paste from the 'Criteria' sheet (columns D:L) to the 'ClientHoldings' sheet (pasted to BA:BI) for each identifier, then loop through to the next identifier.

I would normally populate the range BA:BI using simple VLOOKUP formulas, but I'm asked to do this using only VBA.

Hopefully understood, since I can't use the forum tools from my work PC.

Would appreciate all assistance.

Thanks

 

[StephenCrump]

I would normally populate the range BA:BI using simple VLOOKUP formulas, but I'm asked to do this using only VBA.

A work directive, not to use formulae? Or perhaps some sort of student assignment, to help you learn VBA coding?

If the former, I'd use VLOOKUP myself, perhaps something like this:

Dim lLastRow As Long

lLastRow = Worksheets("ClientHoldings").Range("F" & Rows.Count).End(xlUp).Row

With Worksheets("ClientHoldings").Range("BA7:BI" & lLastRow)
    .Formula = "=VLOOKUP($F7,Criteria!$C:$L,COLUMN()-COLUMN($BA7)+2,)"
    .Value = .Value
End With

But if this is a VBA coding exercise, I expect it's intended to teach you how to loop through ranges, and therefore requiring a different approach. If this is the case, it would be good if you could post whatever code you've come up with so that we can help you with that.

 

[auto.pilot]

Certainly not school... I'm over 50 years old! This is just part of a larger workbook. When possible, we are trying to limit formulae to reduce file size. Speed is enhanced.

Thanks for the suggestion. I will give it a look.

Jim

A work directive, not to use formulae? Or perhaps some sort of student assignment, to help you learn VBA coding?

If the former, I'd use VLOOKUP myself, perhaps something like this:

Dim lLastRow As Long

lLastRow = Worksheets("ClientHoldings").Range("F" & Rows.Count).End(xlUp).Row

With Worksheets("ClientHoldings").Range("BA7:BI" & lLastRow)
    .Formula = "=VLOOKUP($F7,Criteria!$C:$L,COLUMN()-COLUMN($BA7)+2,)"
    .Value = .Value
End With

But if this is a VBA coding exercise, I expect it's intended to teach you how to loop through ranges, and therefore requiring a different approach. If this is the case, it would be good if you could post whatever code you've come up with so that we can help you with that.