VLookup Q 1

[willyboy58]

I have the following info in my Clntrate table:
(the actual table has about 300 people listed)

Number Name Rate
1000 Bob 2.50
1250 Steve 1.25
1500 Mike 3.00
1569 Frank 4.50

I am wanting to select and move some of this info over to 3 different hours and pay sheets. I have the following code for the move:

Sub aVLookUpCopied()
Range("B2&quot.Select
With Range("B2&quot

'B2 is for the Name
.Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$99, 2, FALSE)"
End With
Selection.AutoFill Destination:=Range("B2:B99&quot, Type:=xlFillDefault

'C2 is for the Rate
Range("C2&quot.Select
With Range("C2&quot
.Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$99, 3, FALSE)"
End With
Selection.AutoFill Destination:=Range("C2:C99&quot, Type:=xlFillDefault

End Sub

The info moves as desired, but then when I run the following procedure to calculate total pay, I get a “Type Mismatch” error message on the 9th person on the FIRST sheet. When I look at the first spreadsheet, the first 8 people calculate as desired, but starting at the 9th, on the spreadsheet I get #NA on down in the name and rate columns (B and C) and my total’s column (AF) stops calculating on the 8th employee. On sheets 2 and 3, I get about half way down and then get the #NA. There is no calculation of the total pay on the 2nd and 3rd sheets yet, so I don’t have any totals.

Sub Calcdollars()
Dim Calcdollars As Single
Range("A2&quot.Select
Do
With Activecell
‘the following line is highlited during the error
Calcdollars = .Offset(0, 2) * (.Offset(0, 28) + .Offset(0, 29))
.Offset(0, 31) = Calcdollars
.Offset(1, 0).Select
End With
Loop Until Activecell = ""
Range("AE7&quot.Select
End Sub

Three questions: 1) Why am I getting a Type Mismatch error? 2) Why does it calculate for the first 8 and not for the rest? There are no empty fields in the clntrate sheet. 3) How can I change the line .Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$99, 3, FALSE)" to select all the “current region “ instead of just A2:C99? All info is exported from another program into Excel and more info is added every month and a hard coded range isn’t practical.

TIA

Bill

 

[SkipVought]

Hi,

I'm not following the first error you're talking about. Sounds like one of the values in your calculations on that particular row is NOT a numeric value. Put in a break and check for the .OFFSET values in the Watch Window.

Regarding selecting the CurrentRegion, there are a number of techniques that can be used.

1. reference any cell within the range and envoke the CurrentRegion Method...

With Range("C2").CurrentRegion
   lFirstRow = .Row
   lLastRow = .Rows.Count - lFirstRow + 1
   iFirstCol = .Column
   iLastCol = .Columns.Count - iFirstCol + 1
   .Interior.Color = vbRed
   .Font.Bold = True
...
End With

Of you can use the End Method, provided that there are no empty cells in the desired range...

With Range(Cells(3, 2), Cells(3, 2).End(xlDown)))
'this will have the same row range attributes as above
End with

Hope this helps


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

From my previous post, I have fixed the Type Mismatch and calculating only the first 8 rows problems, but I am still having trouble with the line .Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$99, 3, FALSE)". I created the following Public Functions to select the range of cells. The functions are at the top of the module.

Public Function SelectDownName()
Dim ClientNames As String
ClientNames = Range(Activecell, Activecell.End(xlDown)).Select
End Function

Public Function SelectDownRate()
Dim ClientRates As String
ClientRates = Range(Activecell, Activecell.End(xlDown)).Select
End Function

I then placed these functions in the Selection lines below. This is the range I want to fill in with names and rates, but not go past the last record with NA in the unused cells to the bottom of the sheet.

Sub aVLookUpCopied()
'B2 is Name
Range("B2&quot.Select
With Range("B2&quot
.Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$999, 2, FALSE)"
End With
Selection.AutoFill Destination:=Range(SelectDownName), Type:=xlFillDefault

'C2 is the Rate
Range("C2&quot.Select
With Range("C2&quot
.Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$999, 3, FALSE)"
End With
Selection.AutoFill Destination:=Range(SelectDownRate), Type:=xlFillDefault
End Sub

When I run the procedure, I get the message “Method ‘Range’ of object ‘Global’ failed” and the following line is highlighted.

Selection.AutoFill Destination:=Range(SelectDownName), Type:=xlFillDefault

When I substitute the variables ClientNames and ClientRates, I get the same error.

What am I doing wrong? Any help will be greatly appreciated.

TIA. Bill

 

[SkipVought]

You might want to put the formula into the .Formula property instead of the .Value property.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Skip,

Same error message. Starting at B2, column B is highlighted all the way down to the end of the sheet, way past my last cell of info. The name of the first employee is in cell B2, as it should be, but after that, nothing except highlighted cells. It seems to me that after it fills the first cell with the name, it should then pick up the rate. But it stops after filling in the first name.

When I change the two Public Funtions to Public Subs and insert the variable "ClientNames", I go past the line Selection.AutoFill Destination:=Range(ClientNames), Type:=xlFillDefault to the line Selection.AutoFill Destination:=Range(SelectDownRate), Type:=xlFillDefault. I then receive an error message of Expected Function or variable.

When I change the Public Sub SelectRateDown back to a function, I then get the message on the line Selection.AutoFill Destination:=Range(ClientNames), Type:=xlFillDefault "Method 'Range' of 'Global' failed.

Any ideas? TIA.

 

[SkipVought]

Sorry I did not catch this earlier, but your functions need to return the range address...

Dim ClientNames As String
    ClientNames = Range(Activecell, Activecell.End(xlDown)).Address
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Skip,
Sorry to keep bugging you about this, but I am still getting the message “Method ‘Range’ of object ‘Global’ failed”. I added .Address as you suggested.

Public Function SelectDownName()
Dim ClientNames As String
ClientNames = Range(Activecell, Activecell.End(xlDown)).Address
End Function
Public Function SelectDownRate()
Dim ClientRates As String
ClientRates = Range(Activecell, Activecell.End(xlDown)).Address
End Function

I placed the functions in the code below:
Sub aVLookUpCopied()
Range("B2&quot.Select
With Range("B2&quot 'B2 is Name
.Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$999, 2, FALSE)"
End With
Selection.AutoFill Destination:=Range(SelectDownName), Type:=xlFillDefault

Range("C2&quot.Select 'C2 is the Rate
With Range("C2&quot
.Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$999, 3, FALSE)"
End With
Selection.AutoFill Destination:=Range(SelectDownRate), Type:=xlFillDefault
End Sub

It places the first name that was looked up into B2, as it should, but then the error pops up. The rate is not pulled from the Clntrate table. I even substituted the variables ClientNames and ClientRates into the line. Same error message.

I’m not sure, but I have a feeling that it has to do with the variables ClientNames and ClientRates and how they are being used to call for the range of data. The above functions are the only places where they appear in the procedure.

Thoughts?

TIA. Bill

 

[SkipVought]

Try this. I assume that you only want formulas filled in as fas south as yo have data in column A...

Sub aVLookUpCopied()
    With Range("B2")      'B2 is Name
        .Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$999, 2, FALSE)"
        .AutoFill Destination:=Range(Cells(.Row, .Column), Cells(.Row, .Column).End(xlDown)), Type:=xlFillDefault
    End With
    
    Range("C2").Select   'C2 is the Rate
    With Range("C2")
        .Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$999, 3, FALSE)"
        .AutoFill Destination:=Range(Cells(.Row, .Column), Cells(.Row, .Column).End(xlDown)), Type:=xlFillDefault
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Skip,

It works. But when it reaches the code, the whole procedure slows to a crawl. I believe it's because it is calculating every row on the spreadsheet. In the B column, it has NA all the way down to row 65,000 and something. I tried adjusting the following line by adding .Select after .End(xlDown). I thought that this would select only to the last row of data. I got the
“Method ‘Range’ of object ‘Global’ failed" message.
I also tried .Address, but it did not speed up any.

.AutoFill Destination:=Range(Cells(.Row, .Column), Cells(.Row, .Column).End(xlDown)), Type:=xlFillDefault

We're very close. Thanks for the help.

 

[SkipVought]

Tell me what's in Col A?

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Column A in both sheets is the employee number. It is the value that I am using to look up the employees name and pay rate from the "clntrate" sheet to copy to the destination sheet. Column B on both sheets is for the name and column C on both sheets is the rate.

Thanks.

 

[SkipVought]

Sorry,
I think I must have copied the wrong stuff yesterday.

Sub aVLookUpCopied()
    With Range("B2")      'B2 is Name
        .Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$999, 2, FALSE)"
        .AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
    End With
    
    Range("C2").Select   'C2 is the Rate
    With Range("C2")
        .Value = "=VLOOKUP(A2, [propxfer.xls]clntrate!$A$2:$C$999, 3, FALSE)"
        .AutoFill Destination:=Range(Cells(.Row, .Column), Cells(Cells(2, 1).End(xlDown).Row, .Column)), Type:=xlFillDefault
    End With
End Sub

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[willyboy58]

Skip,

ABSOLUTELY AWESOME!!!!!

Thanks. I have the different days counted and total pay figured for each employee, now I have to run my totals. I'm just about done. I'll have about 25 different procedures all wrapped into one when it's finished.

THANKS!!!!!!!!!!!!!!!!

Bill