Mismatch Error, vba, vlookup, EXCEL

assistantyinka

New Member
Joined
Mar 29, 2013
Messages
2
First I want to apologize in advance for pasting my code like an amateur. I am.
Simply put the VLOOKUP line is giving me a runtime error 13: type Mismatch. Why? and help!

Sub AddStaticInfo()
'because when the active sheet is saved, I want the data in it to be frozen for future reference and not update everytime the saved file is opened'
Range("C20") = Now
Range("D30") = Now
'These are dates'
Range("B4") = Range("B4").Value
'this is amount in words'
Range("D4") = Range("D4").Value
'this is amount in figure'
Range("B8") = Range("B8").Value
'this is address'​
End Sub
Sub ClearValues()
Range("B4").ClearContents
Range("D4").ClearContents
Range("B8").ClearContents
Range("B2").ClearContents
Range("B30,C33,C34").ClearContents​
End Sub
Sub NextFiveDay()
Range("C43").Value = Range("C43").Value + 1
'cosmetic: like an invoice number: helps to keep files for the same people unique'
'the formulas below pull the amount in words and number for the name typed in cell B2'
Range("B4").Value = " = VLOOKUP( " * "&B2&" * ",'C:\Users\Tria Adelfi\Documents\Five Day\[balances (USED FOR NOTICES).xlsm]Sheet1'!$A$2:$G$197,4,FALSE)"
Range("D4").Value = " = VLOOKUP(" * "&B2&" * ",'C:\Users\Tria Adelfi\Documents\Five Day\[balances (USED FOR NOTICES).xlsm]Sheet1'!$A$2:$G$197,3,FALSE)"
Range("B8").Value = " = VLOOKUP(" * "&B2&" * ",'C:\Users\Tria Adelfi\Documents\Five Day\[balances (USED FOR NOTICES).xlsm]Sheet1'!$A$2:$G$197,2,FALSE)"
Range("C20") = "=Now()"
Range("D30") = "=Now()"​
End Sub
Sub SaveFiveDayWithNewName()
AddStaticInfo
Dim NewFN As Variant
' Copy Five Day to a new workbook'
ActiveSheet.Copy
NewFN = "\\RSC-13483\Users\Tria Adelfi\Documents\Five Day\FD_" & Range("B2").Value & "_" & Range("C43").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
ClearValues
NextFiveDay​
End Sub
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Try this.
Code:
    Range("C43").Value = Range("C43").Value + 1
    'cosmetic: like an invoice number: helps to keep files for the same people unique'
    'the formulas below pull the amount in words and number for the name typed in cell B2'
    Range("B4").Formula = "=VLOOKUP( ""*""&B2&""*"",'C:\Users\Tria Adelfi\Documents\Five Day\[balances (USED FOR NOTICES).xlsm]Sheet1'!$A$2:$G$197,4,FALSE)"
    Range("D4").Formula = "=VLOOKUP( ""*""&B2&""*"",'C:\Users\Tria Adelfi\Documents\Five Day\[balances (USED FOR NOTICES).xlsm]Sheet1'!$A$2:$G$197,3,FALSE)"
    Range("B8").Formula = "=VLOOKUP( ""*""&B2&""*"",'C:\Users\Tria Adelfi\Documents\Five Day\[balances (USED FOR NOTICES).xlsm]Sheet1'!$A$2:$G$197,2,FALSE)"
    Range("C20") = "=Now()"
    Range("D30") = "=Now()"
 

assistantyinka

New Member
Joined
Mar 29, 2013
Messages
2
IT WORKED!!!!
FIRST TIME USER
NOW A CONVERT TO THIS FORUM!!!
(do you mean to tell me that doubling up on the quotes around the asterisk was all it took? Why? If there's something else you did that I missed what was it?)

OMG Norie! Thank you! Thank you! for your prompt and, most importantly, problem fixing response.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Double up the quotes, changing Value to fomula, remove a few extraneous spaces, that was about it.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,335
Messages
5,641,555
Members
417,220
Latest member
lam150498

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
Top