VBA vlookup iserror with autofill to dynamic range - preset variables

S

squarah

New Member
Joined
May 2, 2014
Messages
4
Thanks very much for your help on my last question.

Here I am trying to use vlookups in vba with variables that are set depending on which worksheet I am working on.

I want to loop through a column (C) doing vlookups until the last row and enter the value 'unknown' on an error. For each worksheet I work on the table_array and column_index will change. So i want to be able to preset these values in case statements before calling the function temp_for_calcs as below.

Code: 
If ActiveWorkbook.Worksheets(i).Name = "valve" Then ''''VALVE'''''''''''    
          For case_no = 1 To 4
            Select Case case_no
        
                   Case 1
                   
                    Set filter_range = Worksheets(i).Range("j:j,s:s")
                    Set calcs_sheet = Sheets("valve_length_calcs")
                    Set tables_sheet = Sheets("valve_length_tables")
                    Set table_Array = Sheets("flag_matrix").Range("a:be")
                    Set col_index = 57

here is the code I want to adapt to make it accept preset variables for the vlookup. I have tried to use application.worksheetfunction.vlookup and application.vlookup but when I try and autofill these to the end of my column they autofill the values not the formula.


Code: 
Private Function temp_for_calcs_more()'''''' calculations and autofill with variable lengths ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Set myrange = Sheets("temp_for_calcs").Columns("c:c") 'setting range for countif of variable length column
Sheets("temp_for_calcs").Select
Range("d2").Formula = "=countif(b:b,c2)" 'counting number of each unique flag in column a
countnonblank = Application.WorksheetFunction.CountA(myrange) 'counts how many values in b


Range("e2").Formula = "=IFERROR(VLOOKUP(c2,flag_matrix!A:be,57,FALSE),""unknown"")" '%%%%%NEED TO MAKE THIS DYNAMIC%%%%%%
Range("f2").Formula = "=IF(e2<>""unknown"",d2*e2,""unknown"")" ''flag score * no flags
Range("i2").Formula = "=sum(d:d)" 'total flag count
Range("g2").Formula = "=IF(e2<>""unknown"",d2/$i$2 *100,""unknown"")" '% of total score


If countnonblank > 2 Then 'accounts for 1 value and column header, stops autofill error if no rows to fill
 ' fills to the end of data in row b
'''CHANGE BELOW''' table array and column index number'
Range("d2").AutoFill Destination:=Range("d2:d" & countnonblank)
Range("e2").AutoFill Destination:=Range("e2:e" & countnonblank) ------------->>>> autofills values if i use application/applicationworksheetfunction
Range("f2").AutoFill Destination:=Range("f2:f" & countnonblank)
Range("g2").AutoFill Destination:=Range("g2:g" & countnonblank)


End If

If anyone can shed any light on this I'd REALLY appreciate it :eek:
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
Hi and Welcome to MrExcel, Instead of using AutoFill, use FormulaR1C1 to place the formulas in the range in one step.

For example (untested)...
Code: 
Range("e2:e" & countnonblank).FormulaR1C1="=IFERROR(VLOOKUP(RC3,flag_matrix!C1:C57,57,FALSE),""unknown"")"
 
Upvote 0
Jerry Sullivan said:
Hi and Welcome to MrExcel, Instead of using AutoFill, use FormulaR1C1 to place the formulas in the range in one step.

For example (untested)...
Code: 
Range("e2:e" & countnonblank).FormulaR1C1="=IFERROR(VLOOKUP(RC3,flag_matrix!C1:C57,57,FALSE),""unknown"")"
[/QUOTE

Hi, thanks for your response. The problem is that this work needs to be automated so that it will change the table_array and column_index based on which sheet the macro finds and which case has not been satisfied already so i need to be able to set these as variables. Is there a way of doing this or do I need to approach the problem in another way?

Ie

Code: 
If ActiveWorkbook.Worksheets(i).Name = "valve" Then ''''VALVE'''''''''''
    
          For case_no = 1 To 4
            Select Case case_no
        
                   Case 1
                   
                    Set calcs_sheet = Sheets("valve_length_calcs")
                    Set tables_array = Sheets("flag_matrix").Range("a:be") <-----------changes here
                    Set col_index = 57  <----------------------------------------- changes here


                    
                    Call filter(filter_range, calcs_sheet, tables_sheet)
                    Call area_flag(calcs_sheet)
                    Call temp_calcs(calcs_sheet)
                                            
                   Case 2
                   
                    Set calcs_sheet = Sheets("valve_length_calcs")
                    Set tables_array = Sheets("flag_matrix").Range("a:bf") <-----------changes here
                    Set col_index = 58 <----------------------------------------- changes here

                    
                    Call filter(filter_range, calcs_sheet, tables_sheet)
                    Call area_flag(calcs_sheet)
                    Call temp_calcs(calcs_sheet)

                                                  
                   Case 3 ...............................

                   Case 4................................

Much appreciated :cool:
Click to expand...
 
Upvote 0
I need to do this so that it will perform vlookups for 14 different table arrays/column indexes in worksheets of varying length, repeating the vlookups for each table array up to 100 times.
 
Upvote 0
squarah said:
Hi, thanks for your response. The problem is that this work needs to be automated so that it will change the table_array and column_index based on which sheet the macro finds and which case has not been satisfied already so i need to be able to set these as variables. Is there a way of doing this or do I need to approach the problem in another way?
Click to expand...

Sorry, my focus went to your comments in the bottom half of your OP regarding problems with AutoFill and I didn't catch that your primary question was about making the lookups dynamic using variables for the Vlookup parameters.

Yes, you can do that. Your code examples show you already have some functions that are passing parameters to called procedures. You can do the same with your temp_for_calcs_more function. Something like:

Code: 
Private Function temp_for_calcs_more(ByVal table_Array As Range, _
   ByVal col_index As Long)
 '......
 '......
 Dim lLastRow As Long
 Dim sR1C1Address As String

 '......
 '......
 With Sheets("temp_for_calcs")
   '--get last row with data in column c
   lLastRow = .Cells(.Rows.Count, "c").End(xlUp).Row

   sR1C1Address = table_Array.Address( _
      ReferenceStyle:=xlR1C1, External:=True)
   
   .Range("e2:e" & lLastRow).FormulaR1C1 = _
       "=IFERROR(VLOOKUP(RC3," & sR1C1Address & _
       "," & col_index & ",FALSE),""unknown"")"
   '......
   '......
 End With

 '......
 '......

End Function

Note: Finding the Last Row of data as shown above is probably a better fit than using CountA if you might have any blanks.

For your calling procedure, try to minimize duplication (for consistency and ease of maintenance) by clustering common steps outside the Case statements....

Code: 
Sub CallingProcedure()
 '......
 '......

 If ActiveWorkbook.Worksheets(i).Name = "valve" Then
   Set calcs_sheet = Sheets("valve_length_calcs")
   
   '--if possible, lookup col_index to make more dynamic
   col_index = Application.Match("Design Temp", rHeaders, 0)
   
   For case_no = 1 To 4
      Select Case case_no
         Case 1
            Set tables_array = Sheets("flag_matrix").Range("a:be")
            col_index = 57 'could override default here

         Case 2
            Set tables_array = Sheets("flag_matrix").Range("a:bf")
             '......
             '......
          Case Else
             '......
             '......
      End Select 
   Next case_no
 End If
  '......
 '......
                  
 Call Filter(filter_range, calcs_sheet, tables_sheet)
 Call area_flag(calcs_sheet)
 Call temp_calcs(calcs_sheet)
 Call temp_for_calcs_more(table_Array, col_index)
End Sub

That should work, although without seeing your entire picture it's hard to say if there isn't a better approach.
 
Last edited:
Upvote 0
One other item I just noticed that could help you reduce the number of Cases that need to be maintained.

In your example....

squarah said:
Code: 
If ActiveWorkbook.Worksheets(i).Name = "valve" Then ''''VALVE'''''''''''
    
          For case_no = 1 To 4
            Select Case case_no
        
                   Case 1
                   
                    Set calcs_sheet = Sheets("valve_length_calcs")
                    Set tables_array = Sheets("flag_matrix").Range("a:be") <-----------changes here
                    Set col_index = 57  <----------------------------------------- changes here


                    
                    Call filter(filter_range, calcs_sheet, tables_sheet)
                    Call area_flag(calcs_sheet)
                    Call temp_calcs(calcs_sheet)
                                            
                   Case 2
                   
                    Set calcs_sheet = Sheets("valve_length_calcs")
                    Set tables_array = Sheets("flag_matrix").Range("a:bf") <-----------changes here
                    Set col_index = 58 <----------------------------------------- changes here

                    
                    Call filter(filter_range, calcs_sheet, tables_sheet)
                    Call area_flag(calcs_sheet)
                    Call temp_calcs(calcs_sheet)
Click to expand...

If many or all of your tables_array ranges have the same Leftmost column, you can set tables_array in one place to the greatest number of columns you'll need...

Code: 
   Set tables_array = Sheets("flag_matrix").Range("[B][COLOR="#0000CD"]a:bz[/COLOR][/B]")
     
   For case_no = 1 To 4
      Select Case case_no
         Case 1: col_index = 57
         Case 2: col_index = 58
         Case 3: col_index = 64
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
AutoFill or Copy Formula to All Relevant Cells
Replies
5
Views
124
deZine
D
N
  • Solved
sometimes problem with vba (Error 400)
Replies
5
Views
213
Domenic
D
J
  • Question
Autofill Dynamic Selection Range
2
Replies
15
Views
867
Joe4
Joe4
G
  • Question
VBA - add formula to cell when created
Replies
2
Views
331
Golaidron
G
C
  • Question
Autofill formulas
Replies
2
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,045
Messages
6,128,486
Members
449,455
Latest member
jesski

Share this page

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