fill cells based on contents of other cells

0
votes

In col A I have a list of c++ data types in sheet 3 of my workbook In col C I have the number of bytes associated with each data type.

In sheet 1 col A I have a pull down selection box based on the data types in sheet 3.

I have a button to run a Macro to populate col C on this sheet with the associated data size from sheet 3.

Data is thus:

  • col A col C
  • char 8
  • int 16
  • long 32

etc etc

I find the total number of rows populated with: Lastrow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row

That is the max row count for my loop how can I make the comparison for each row in col A sheet 1 against each row in col A on sheet 3 in order to grab the correct value from col C on sheet 3. and put it into the corresponding row in col C on sheet 1. Thnks.

1
vbaexcel
Don't need VBA for this, just use a VLOOKUP formula: =VLOOKUP(A1,Sheet3!A:C,3,FALSE) – tigeravatar
Thanks tigeravatar. I previously built this function using the insert fx feature. It works in the builder window, returns the correct value but does not leave the value in the cell where the formula is inserted, only the formula appears. – user2220844

1 Answers

0
votes

this can be done in multiple ways. But if you want to do it by vba, Put the following code button_click sub. Lastrow can be calculated as you did.

Dim searchRange As Range

Set searchRange = Worksheets("Sheet3").Range("A1:A3")
For i = 1 To Lastrow
matchedrow = WorksheetFunction.Match(ActiveSheet.Range("A" & i), searchRange, 0)
ActiveSheet.Range("C" & i) = Sheet3.Cells(matchedrow, 3).Value
Next i