[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 541
  • Last Modified:

Need to Transfer (Convert) Text to Numbers via VBA & VLookup!

Hi!

In my scenario data's being dumped in from SQL into Excel 2003, everything as text strings. Excel needs to convert some of these strings to real numbers and then cross-reference them with VLookup formulas created by VBA to get their text equivalent (1=YES,0=NO,etc.) in a Lookups tab.

The code I have now works half the time, but the other half I get I get the dreaded #N/A's!!!

The attached Excel file is pared down to the bare essentials.

That data appears as it is just before opening, after new data has been dumped from SQL. As soon as the user opens the file all the VBA will run automatically.

The VBA to format and convert the data in the Borrower Consents spreadsheet is in the cDynTab_BorCon module. The text-to-number sub-routine - ConvertTxtToNum - is in the cDynamicProcedures module.

I'm a long-time Access developer but this is the most extensive job I've ever done in Excel. I admit a VLookup range in Col. Z of the Borrower Consents tab to transfer values back and forth between Borrower Consents and the Lookups tab is probably a bit klunky. Maybe my problem would be solved with a slicker and more elegant way to get the data back & forth with VBA instead of having to relay on a VLookup range...???

IMPORTANT NOTE: Whichever code is executed - Opening or Closing - is determined by the boolean variable blnOpeningProc (Opening Procedures). I copied it to the top of the  cDynTab_BorCon module already set to true:

  ' set for Opening Procedures (TRUE) or Closing Procedures (FALSE)
  ' ------------------------------------------------------------------------
      blnOpeningProc = True

Whatever assistance anyone can offer would be greatly, GREATLY appreciated!!!

Thank you very much in advance!
Need-To-Convert-Text-to-Numbers.xls
0
monbois
Asked:
monbois
  • 5
  • 4
  • 3
1 Solution
 
ScriptAddictCommented:
could you have them dump into a different cell and then just use the =value("cellref") on the cell u want to use them in?

Or try one of these vba solutions:
Cint()
Cdbl()
cdec()

I also remember evaluating them works.
=EVALUATE(A1)


Another approach

Sometimes you can tell Excel that it is a number by just adding 0 to the cell.

0
 
ScriptAddictCommented:
IE vlookup(A1+0,Table,column,match)
0
 
ScriptAddictCommented:
 
 With Range(szVSrcRange)
        .NumberFormat = "General"
        With .SpecialCells(xlCellTypeConstants).Cells
            .Value = Evaluate(.Value)
        End With
    End With

Open in new window

0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
monboisAuthor Commented:
Thanks, but that didn't work. In fact, it changed all the number to 1's!
0
 
ScriptAddictCommented:
Try this one:

With Range(szVSrcRange)
        .NumberFormat = "General"
        With .SpecialCells(xlCellTypeConstants).Cells
            .Value = .Value+0
        End With
    End With
0
 
redmondbCommented:
Hi, monbois.

Problem is that the cells are formatted as Text. Simply change their formatting to General (or whichever format(s) you require), copy a cell with the number 1 in it and then do a "PasteSpecial - Values - Multiply". Excel will then treat all the selected cells as "normal" numbers.

["PasteSpecial - Values - Multiply" - i.e. select PasteSpecial, tick "Values" and "Multiply". Then click on "OK".]

Regards,
Brian.
0
 
monboisAuthor Commented:
ScriptAddict - Thanks again, but again it didn't work - Type Mismatch error.

redmondb - Thanks for your suggestion, but if it were as easy as changing the formatting I never would've posted my question in the first place. The data is downloaded from SQL as text and if you look at the VBA in the attached Excel file, I switch all data formatting to numbers first because I thought that solved this problem, and it did - for my development copy. But the SQL developer has informed me that it's not working with his freshly downloaded data, which is how I'm testing it now with all the little green-corner error messages in each cell. The conversion code itself switches formatting from numbers to "General" so formatting isn't the issue.

Also, this has to be an automated VBA process, it can't be a manual one. I included a single dynamic data spreadsheet with the attached file, but there are half a dozen more. Besides, this is for a client's clients, and my client does not want to tell his clients they have to manually reformat spreadsheets.

You guys or anybody else got anything else?
0
 
redmondbCommented:
monbois,

The file you posted has the cells in Text format. I went through the steps I mentioned and the number fields are now fine. It works.

Please try my steps in the file you posted. Once you're happy with that, it's straightforward to put the steps into a macro. Let me know then and I'll do it.

Regards,
Brian.

0
 
redmondbCommented:
monbois,

A "quick and dirty" for the version you posted...
Sub Macro2()
    Range("B6:C15,G6:K15").NumberFormat = "General"
    Range("L1").FormulaR1C1 = "1"
    Range("L1").Copy
    Range("G6:K15,B6:C15").PasteSpecial Paste:=xlPasteValues, Operation:=xlMultiply, SkipBlanks:=False, Transpose:=False
    Range("L1").ClearContents
End Sub

Open in new window


Regards,
Brian.
0
 
monboisAuthor Commented:
Thanks for your suggestion Brian (redmondb), but I ran your code and it works perfectly in reformatting the text values to numbers. That's not the problem, though. The problem is that the MATCH formula in Col. Z is still not recognizing the values in Col. B as numbers and it's not cross referencing in order to provide the text string.

Here's the Col. Z formula written by VBA:

=IF((BorCon_ConType=""),"",INDEX(BorCon_ALL,MATCH(BorCon_ConType,ConsentTypeID,0),0))

It's a reverse VLookup searching for the text string in the Lookups tab by cross-referencing the number in Col. B:

  CMBS_ConsentType      consent_type_id
  ASSUMPTION      1
  DEFEASANCE      2
  LEASING                           3
  PARTIAL RELEASE      4
  CAPEX RESERVES      5

This is what happens when the file opens and copies data from the FieldMapping tab (which I deleted from the attached Excel file because it's function is out of scope for this query). When the file closes the VBA writes a straightforward VLookup formula, but there's no problem with that because it's always string text cross-referencing string text to find the matching numbers.

I hope this more clearly explains what my dilemma is. I do appreciate all you assistance.

Thanks!
0
 
monboisAuthor Commented:
ScriptAddict and redmondb - Thank you for all your help, but I just discovered that all along the fault was in my reverse VLookup MATCH formula!

Yes, I do feel stupid.

Thanks for all your effort, especially redmondb.
0
 
monboisAuthor Commented:
THANKS!
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

  • 5
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now