Row and Column Count for a lookup array

Kurt

Well-known Member
Joined
Jul 23, 2002
Messages
1,664
Can someone show me the best way to do a Row and Column count for this lookup array?

[ ReDim aTrades(1 To 4, 1 To 2) As Variant
ReDim aSystemCodes(1 To 69, 1 To 2)
'todo: do a row and column count of the above lookup arrays and use variables
/]
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
My bad I need to give some more info.

This is the fill value arrays:

'fill values arrays
aRawData = Sheet5.Range("A2:S" & rdLastRow).Value
aTrades = EstShtCodes1.Range("TradeLookup").Value
aSystemCodes = EstShtCodes1.Range("SystemCodeLookup").Value

The EstShtCodes1. Range contains the following values:

ESTmep
Trade Trade
Mechanical Piping MECH
Medical Gas PLBG
Plumbing PLBG
Process Piping MECH

These values are on a worksheet named EstShtCodes in cells K32-L35

These are the SystemCodeLookup values and they are on the same worksheet EstShtCodes in cells H3:L71

Chilled Water Return CHA
Chilled Water Return UG CHU
Chilled Water Supply CHA
Chilled Water Supply UG CHU
Compressed Air MPS
Condensate Drain CDA
Condenser Water Return CWA
Condenser Water Return UG CWU
Condenser Water Supply CWA
Condenser Water Supply UG CWU
Equalizer CWA
Filter Water Return MIP
Filter Water Supply MIP
Heating Water Return HWA
Heating Water Return UG HWU
Heating Water Supply HWA
Heating Water Supply UG HWU
Industrial Water Return MIP
Industrial Water Supply MIP
Make-Up Water DWA
Mechanical Vent MIP
Natural Gas NGA
Natural Gas UG NGU
Natural Gas Vent NGA
Refrigerant Gas REF
Refrigerant Liquid REF
Steam Condensate - HP STM
Steam Condensate - LP STM
Steam Condensate - MP STM
Steam HP STM
Steam LP STM
Steam MP STM
Carbon Dioxide MED
Hydrogen MED
Medical Air MED
Medical Vacuum MED
Nitrogen MED
Nitrous Oxide MED
Oxygen MED
Oxygen Evacuation MED
Waste Anesthesia Gas Disposal MED
Acid Waste MPS
Deionized Water MPS
Domestic 140° Hot Water DWA
Domestic Cold Water DWA
Domestic Cold Water UG DWU
Domestic Hot Water DWA
Domestic Hot Water Return DWA
Domestic Hot Water UG DWU
Domestic Tempered Water DWA
Grease Waste GWA
Grease Waste UG GWU
Non-Potable Water DWA
Sanitary Vent SWA
Sanitary Waste SWA
Sanitary Waste UG SWU
Storm Drain SDA
Storm Drain Overflow SDA
Storm Drain UG SDU
Sub-Soil Drain UG SDU
Sump Pump Discharge MPS
Acetylene MIP
Argon MIP
Carbon Dioxide MIP
Helium MIP
Hydrogen MIP
Instrument Air MIP
Nitrogen MIP
Vacuum MIP

Sorry I didn't have enough coffee to think this through when I posted earlier!
 
Upvote 0
If you just want to find the size of the array, try something like
Code:
   Dim ardRw As Long, ardCol As Long
   Dim aRawData As Variant
   aRawData = Sheet5.Range("A2:S" & rdLastRow).Value
   ardRw = UBound(aRawData)
   ardCol = UBound(aRawData, 2)
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,219
Members
452,619
Latest member
Shiv1198

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top