Free Microsoft Excel 2013 Quick Reference

Vba code help convert to number Results

After importing my report to Excel my numbers are listed in text format with the green arrow in the upper corner (see attached worksheet). For my situation, the problem can easily be fixed by highlighting all of the fields and selecting "Convert to Number" from the exclamation point drop down.

Is there a way using VBA Code to replicate this process? I would like all of my values to retain their format (percentage, dollar, etc). but no longer have the green notices in the upper corner.

Any help would be greatly appreciated. Thanks!

Hi all

I have a spreadsheet which has extracted values from another program. They values are returned as text i.e. '100 as opposed to a number which would be 100. Also nil values are returned as '— instead of a 0.

I can convert the '100 type text to numbers in vba but cant figure out how to convert the '— values.

I have attached my workbook which has the code for converting numbers. You will see it crashes when it hits a '—.

Many thanks for your help in advance.

Ashley

All Excel Helpers,

I have a spreadsheet that contains numbers formatted as text in column A. Some of the numbers are prefixed with alphabets (e.g., they are either say, (1) 999118 or (2) M990695). The file I get every month does not have the same number of (1) or (2). What I am trying to in column B is to convert the numbers formatted as text in column A to numbers in column B using the formula (=1*A2). The formula works OK when they are not prefixed with alphabets. Otherwise, I get “#VALUE”. I want to write VBA codes to say that if column B is ‘#VALUE” in that cell, then copy column A into that cell. For example, B17 should now have what is in A17 which is M990695.
I can do this manually but can it be done with VBA?

A sample file is attached

Thank you for all help

Angela

Hi,
I'm looking for some code to convert numbers stored as text to numbers.

Can anyone help me with this please?

Thanks

Dear Experts,

We have developed an application in VB6. This application exports some
results to Excel. The Excel displays error "The number in this cell is
formatted as text or preceded by an apostrophe". An exclamation sign
appears with the cell offers "Convert to Number" option. We have
controlled all this through PasteSpecial VBA code within our
application.

xlBook.ActiveSheet.Range("Z7").Copy
xlBook.ActiveSheet.Range("A1:X10").PasteSpecial Paste:=xlPasteValues,
Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

This code work fine if we use dot (.) as decimal separator. However for
another locale setting where comma (,) is used as a decimal separator,
the above block of code does not convert the values intu number (in
Excel). Can anyone help me out.

Any help will be highly appreciated.

Thanks & Regards,

Faiz

Dear Experts,

We have developed an application in VB6. This application exports some
results to Excel. The Excel displays error "The number in this cell is
formatted as text or preceded by an apostrophe". An exclamation sign
appears with the cell offers "Convert to Number" option. We have
controlled all this through PasteSpecial VBA code within our
application.

xlBook.ActiveSheet.Range("Z7").Copy
xlBook.ActiveSheet.Range("A1:X10").PasteSpecial Paste:=xlPasteValues,
Operation:=xlAdd, SkipBlanks:=False, Transpose:=False

This code work fine if we use dot (.) as decimal separator. However for
another locale setting where comma (,) is used as a decimal separator,
the above block of code does not convert the values intu number (in
Excel). Can anyone help me out.

Any help will be highly appreciated.

Thanks & Regards,

Faiz

I am a java developer. I created an excel template to save in the server. When users request the report, I open the template, write the data in, save and dump throught the servlet to the user's machine

When user opens the excel file, it will show the raw data sheet
When he opens a chart sheet ( the summarized data is already there), the VBA code I wrote will draw the chart

My problem is lot of users are still using Office 03 and when they opens the chart sheet, everyone has different chart. Some got the big font, some got few axis labels since they are too big,

The data is built on fly, so I don't know how to make it look the same for everyone
Please help. Thank you very much

Below is my code

Function buildChart(sh As Worksheet)
'
' buildChart Macro
'
'
    On Error GoTo errHd
    sh.Activate
    
    ' Data will begin at 2nd row
    ' 1st row is the title
    
    Dim cTitle As String
    cTitle = sh.Range("$K$1").Value
    
    Dim aTitle As String
    'aTitle = sh.Range("$L$1").Value
    aTitle = sh.Range("$M$1").Value
     
     
    Dim r As Integer
    r = 2 + Application.Application.CountA(sh.Range("A:A"))
    
    'Find out the max column (by number then convert to letter
    Dim c As Integer
    c = WorksheetFunction.CountA(sh.Range("2:2")) 'row 2 is the groups labels
    'convert to letter
    Dim maxcolumn As String
    maxcolumn = ColumnLetter(c + 1)
    
    
    sh.Range("$A$2", maxcolumn & r).Select
   
    Charts.Add
    ActiveChart.SetSourceData Source:=sh.Range("$A$2", maxcolumn & r), PlotBy:=xlColumns
    ActiveChart.ChartType = xl3DColumnStacked
    
    With ActiveChart
        .HasTitle = True
        .chartTitle.Characters.Text = cTitle
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).axisTitle.Characters.Text = aTitle
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).axisTitle.Characters.Text = "Qty"
        .RightAngleAxes = True
    End With
    
    With ActiveChart.Parent
        .Height = 360
        .Width = 600
        .Top = 100
        .Left = 200
    End With
    
errHd:
    'Don't have to do anything, draw other charts
    
End Function


I have a problem with conversion of numbers to percent, that I hope some of you could help me with…

In the attached sample (with macros enabled), you will find the problem when pressing the button “INDTAST DATA” (I apologize for the linguistic challenge, but the XL-sheets are in Danish… To relief – check the crash course in Danish below) and then entering some number in the two last textboxes (called “Forventet ændring i antal timer I næste kvartal (%)” and “Forventet ændring i omsætning i næste kvartal (%)”)… If you enter something there, the result will be multiplied by 100 in the worksheet.

I would like to be able to simply enter a full number – like 12 or 9,5– which will then be entered into the worksheet as 12% or 9,5% (and not 1200% or 950%)… I think the answer lies in inserting some code in the VBA code, when the macro writes the data to the worksheet, but you guys know more about it than I do...

I can, of course, enter a full number in the textboxes – followed by a %-sign, but that will slow down the process significantly as well as increase the risk of errors…

BONUS: Crash course in Danish (to help with the linguistics)
Virksomhed = Company
Kvartal = Quarter
År = Year
Branche = Industry
Fakturerede timer = Billed hours
Faktureret omsætning = Billed revenue
Timeforventning = Expected hours (next quarter)
Omsætningsforventning = Expected revenue (next quarter)
Indtast data = Enter data

I hope it makes sense and that someone can provide a working solution

Hi all,

I need a little help, In the attached file I want to convert the VLOOKUP() formulas into VBA code.
I have tried searching the forums for something simular without much luck.

This is what I want the code to do (if possible).

In column "A" I enter a product number then the lookup populates the other columns (C,D,E,F,G) from information stored on the other sheets "B" & "PRICE".

The workbook works fine the way it is setup now, but what I am finding it is getting very slow because the two other sheets (B & PRICE) contain 20,000+ lines.
This is okay if the LOOKUP sheet has a couple hundred lines, but over time this is up to about 3000 lines and having to FILL DOWN the formulas is quite time comsuming.

Any help or suggestions to streamline this process would be much appreishated.

Thank you.

Baz

Is there a way to convert a code that you put in a cell like this one HTML Code: 
 into code in vba?

P17 is the cell that I have this coding in - it will determine if there is text in C17 and if there is it will pull up the pricing and markup associated with it and put the resale price in p17.
C17 is text. On another sheet of the workbook there is text that associates with numbers (pricing and markups).
N17 is the price (that is associated with C17)
O17 is the markup (associated with C17)
This goes through a range of P17 to p63 so there would need to be a loop. I was thinking along these lines but....
HTML Code: 
Private Sub Resale_Per_Item()
Dim rpitem As Range
rpitem = "P17:P63"
Dim procell As Range
Set procell = Range(r).Offset(0, -13)
If Range(procell) = "" Then rpitem = ""
if (iserror(vlookup(procell,vwlookup,4,false))
...
end Sub
Anyone have a completely different idea of how to do it or continuing my idea? (the reason I'm wanting it in vba rather than each individual cell is I want to tweak that code to allow the user to enter their own price and markup if they want rather than the one pulled up, but if the code is in the cell then once they type in their numbers the code will be gone (since the cell can't be protected to allow for their input. So the vba code will need to allow for user input, but if there is no user input into cells N(i) and O(i) then it will use the associated numbers.)
Thanks for all help.

I have the string '1/1/05 in cell A1. When I execute the following code:

Range("A2").Value = Range("A1").Value

VBA converts the string to a date (i.e., number), which is not what I want.

I've tried

Range("A2").Value = Cstr(Range("A1").Value)

and even passing the value through a string variable, but the result is the
same. If the string is _1/1/05, then there is no problem. Ideally, I'd like
the contents of A1 to be a date and have it converted to a string when placed
in A2, but that seems too much to ask if I can't even copy a string from A1
to A2. Help appreciated.

Using Excel 97

Tony

Hi All,

Hope everyone are doing good, Am in need of your help.

I have created a payroll application using some VBA and worksheet functions as well, everything is working fine, but I am getting struck in pay slip processing, for Payslip creation I created one sheet and used vlookup function, if I key in the employee number, all other particulars will update, and I have created a mail icon which converts that particular range to pdf and generates email, now its becoming very difficult for me to do the same action for all employees.

So am looking where macro automatically runs and generate email for all at a time, below is the more details

Ex:-
Sheet name “payslip” in cell “A5” I need to update the employee number, I have the list of applicable employee numbers in sheet “Maillist” from “a2 to X (xldown / end)”, now I want to write a code where it works as
1. Copy from sheet “Maillist” cell “a2” and paste in sheet “payslip” cell “A5” and then run the mailer code(converts in to pdf and generates email)
2. Copy from sheet “Maillist” cell “a3” and paste in sheet “payslip” cell “A5” and then run the mailer code(converts in to pdf and generates email)
3. Copy from sheet “Maillist” cell “a4” and paste in sheet “payslip” cell “A5” and then run the mailer code(converts in to pdf and generates email).
.
.
.
Copy from sheet “Maillist” cell “a100”(xldown) and paste in sheet “payslip” cell “A5” and then run the mailer code((converts in to pdf and generates email).

I have the code which automatically convert particular range to pdf and generates email, can anyone help me to get the above code..

Thanks in advance

Regards,
GV Reddy

Hi all,

I am trying to create some code in order to remove characters from a phone number and convert into a string to place back into an excel database. The phone numbers have country codes attatched so only wish to remove all characters.

e.g of a phone number:
0773333333 |44|-2| @

There are over 5000 records of phone numbers so an =MID function is not possible. Any help would be greatly appreciated.

Many Thanks

I have a work pattern for a small number of people and need to chart the number of people that are at work hour by hour over the course of the work pattern.

Each shift is represented as a code i.e. N, EV, D etc, each of these obviously relates to a time value i.e. N = 18:00 - 06:00, EV = 16:00 - 23:00 so on and so forth. However the data is presented as Day, and the shift patterns on that day. The time values are located in a box at the bottom of the sheet. Any ideas on how to go about charting this. I have thought about using a pivot table but the real problem is converting the current format into something that can be charted over a timescale. Please see attached spreadsheet for data layout.

As the pattern runs on for the best part of the year I dont mind viewing only 2 weeks on a chart at a time and changing the data range using vba code later. My problem comes in the format of the data and how to convert it into a chartable form.

Any help would be vey much appreciated as this is a real headache for me and without
re-entering the data manually I can't see a way around, however I am sure someone will see a logical solution.

Regards,

XYLO

In my example imagine that I am writing VBA code that dissects a cell which always contains some alphanumeric characters a colon ":", some more characters, another colon ":" and some more characters.

For Example this is what is in my cell "Deposit:93121:Southern California"

what I need is to cut and paste everything that is located after the 2nd colon ":"

Say that my cell is D433 and in non-vba world I can chop off the first part of the cell to the left of the first colon ":" by using the following formula:

=MID(D433,FIND(":",D433)+1,50)

assuming there are less than 50 characters in my cell, which is a safe number, then I cut and pastespecial this cell as values into the same cell and repeat that same formula and voila, what remains in cell D433 is what I am looking for "Southern California"

My question is how can I replicate this surgical process in VBA, is there a way to combine this process in one command on any given target cell?

Thank you in advance for your kind help.

I have spent months converting the useless report document for my work from its original Word file to the more user friendly Excell.

It works really well and has a few people interested. The problem I have is that I used a lot of consatination and formulas to write the repeated information that goes onto different areas of the report (customer, location, measurements units, etc). This means that pages can not be easily edited out of the finished report as that causes #ERROR and #VALUE messages to spring up all over the place. When we had the older Word version of the report the guys usually spent a day after the job, edditing out the unwanted bits of the report. I want to use Excell to sort of do away with this frustrating exercise while avoiding the #ERROR and #VALUE messages.

What I thought of was a VBA routine that will select the pages for printing To understand. There are fact sheets covering all the equipment we may supply to our customers. These are filled out at the end of the job and the printouts are handed to the customer as their hard copy. Not all the equipment in the report will be used and I have set these pages up so that they remian blank. This works well enough at the moment - the blank pages are easily identified for discarding prior to adding the report to the completed folder.

How I think the programme will best work is that it will look for a value in a cell and if it finds the value of that cell is 1, it will go to the next programme line which will tell excell which area to highlight and then the next line will print that area. The VBA programm will then move onto the next page and identify the cell that holds the value, If it is a zero instead of one then it will skip to the next page, and so on and so on until the the vba programme has reached its end. Ideally this programm will be started by a simple button on the spread sheet itself

I am in the process of shopping around for a book on Excell VBA. I intend to use VBA code in future Excell spreadsheets to automate jobs that are not covered by Excell functions and formulas.

Past experience has been 10 years writing spreadsheets which I use in my job as a technician. Spreadsheets cover number crunching, form filling, data manipulation. Applications used - Psion Sheet, Lotus, Excell. Prior to that I wrote programmes in Basic programming language on a hand held computer to carry out the same work I have just described.

Gone on a bit here but without the detail you folks might not understand what I have been up to or where I might be going with this.

Any advice or help will be most appreciated as I am fed up with colleagues at work telling me how easy it would be, but not coming up with the goods themselves :-/

H and ignore the pages that have been left blank (

I would some advice on populating an array in Excel VBA.

I am using the code below to convert column numbers to their corresponding text values.

	VB:
	
 
Public ColRef(1 To 9) As String 
 
ColRef(1) = "A" 
ColRef(2) = "B" 
ColRef(3) = "C" 
ColRef(4) = "D" 
ColRef(5) = "E" 
ColRef(6) = "F" 
ColRef(7) = "G" 
ColRef(8) = "H" 
ColRef(9) = "I" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and cannot help thinking there has to be a better way, perhaps using a "for loop" or perhaps a completely different approach.

Can you perhaps populate an array when you declare it?

TIA
S G Brock

Hi everybody!!!!!!!!!!!!!!

I am having too much fun in a peverse way.

I would like to add and number and a character:

1000 & "L" = 1000L

I can do this via formula in excel, but I cannot get it to work in VBA.

My problem is converting 1000(numeric) to 1000(text) in VBA.

Any VBA code ideas?

I tried searching without any luck.

I appreciate any help in stoping the fun!!!!!!!!!!

Need your help, please:

The following code works OK to "lock" all sheets in the workbook. For example, if there are 5 sheets and if I triger this code while in sheet 4, it locks all sheets including sheet 5. If there is no sheet 5, it locks all sheets up to 4. However, it is possible to have sheets more than the sheets you wish to "lock".

What I need is to lock only up to sheet 4 and not sheet 5. In other words, could you suggest a modification to the code so that it locks all sheets including the sheet the user is in and NOT the sheets higher.

Sub LockAllSheets()
If strMasterPassword = "" Then
strMasterPassword = Worksheets(1).Range("E100").Value
End If
Dim wsSheet As Worksheet
On Error Resume Next
For Each wsSheet In Worksheets
wsSheet.Unprotect password:=strMasterPassword
wsSheet.Activate
Range("$A$3:$K$84").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Cells.Select
Selection.Locked = True
Selection.FormulaHidden = False
wsSheet.Protect password:=strMasterPassword
Next wsSheet
On Error GoTo 0
End Sub

I thought I had this all cleaned up, but when I ran it on a different data file I found a scenario I hadn't considered.

Background: [This is all done with a pair of VBA subs] A .txt file is imported into Excel. The extraneous data (like page headers etc) is extracted to a garbage sheet (kept only for audit trail purposes) and the good stuff (like data) is extracted to an analysis sheet. Unfortunately because I need the headers to remain in their original form and Excel was reading parts of them as numbers (where fixed-width column import broke them up that way by coincidence) I had to import all my data as text. I then converted the text to numbers by writing a VALUE() formula to the empty columns off to the right and then copying them and pasting as values over the original data.

Okay, so here's the problem. The last column of numbers is really the one that is most important to me. I need to ensure that it contains no error values. On the original .txt report it is possible for there to be ##### in this column where the number was to big for the column width in the original report. This was imported as text, and then converted to #VALUE in my conversion from text to numbers. My original solution was a Columns.SpecialCells.Select that selected all the #VALUE cells to which I then wrote formulas (the difference between the two preceding columns) and converted to values.

So my code worked great while there were some #### cells in the .txt file. However I've just tested out a second .txt file that coincidentally did not have any #### in it. Now I'm getting an error message because SpecialCells doesn't find what it is looking for.

What I need is a way to run the SpecialCells only if I can guarantee that it will find what it is looking for. Keep in mind that this is being run on fairly huge data files (often more than 10,000 rows) so I'd like to avoid slow loops.

It seems to me there's a simple solution to this long-winded problem, but I can't think of it. Perhaps because it is late on a Friday afternoon before a long weekend and I was out "socializing" past midnight last night...

Thanks for your help and have a great weekend everyone.

Kelly.


No luck finding an answer? You could always try Google.