[RESOLVED] help with VBA in Excel

[mikevba]

I am trying to do repetitive actions over a selected range of cells in an Excel spreadsheet. What I am trying to do is to check if the cell is numeric or text. If it is numeric to change it to text. Yes the cells contain numbers and yes we want the numbers to be represented as text for importing the spreadsheet through another process.

I am NEW, very NEW to VB and VBA, so pardon the obviously naive request.

thanks
Mike

[RhinoBull]

Welcome to Forums, mike!

In Excel go to VB Editor and do something like the following sample:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static prevTarget As Range
 
    If Not prevTarget Is Nothing Then
        If Not IsNumeric(prevTarget.Text) Then
            MsgBox "Cell(" & prevTarget.Row & "," & prevTarget.Column & "): Value is not numeric."
        End If
    End If
    
    Set prevTarget = Target
 
End Sub

[Hack]

Excel VBA question moved to Office Development

[DKenny]

Why don't you just make the entire range Text? This would be quicker that looping through all the cells individually.

YourRange.NumberFormat = "@"

[mikevba]

hey thats a great idea!!!

This is why I am here to steal good ideas...

thanks

[DKenny]

BTW, welcome to the forums - your home for great ideas.

Also, don't forget to mark this as resolved.

[mikevba]

I used this, but it only adds leading zeros. It doesn't make it a text field, like I need.

Range("Range1").NumberFormat = "000"

Any ideas?

Mike

[DKenny]

NumberFormat = "@"

is what you should use to mark a cell as TExt.

[mikevba]

Thanks for the help, but it is not quite there yet.....

What I am trying to automate is a fuction that I used to ....

=text(c1,"000") which would add in leading zeros...... and make it a text column....

When I use Range("Range1").NumberFormat = "@" it makes it text but I lose the leading zeros...

thanks for helping a newbie.

Mike

[DKenny]

Something like this maybe?

Sub testformat(InputRange As Range)
Dim sValAsString As String
    
    With InputRange
        sValAsString = CStr(.Value)
        .NumberFormat = "@"
        .Value = Right("000" & sValAsString, 3)
    End With
   
End Sub

[mikevba]

I am struggling here.... the error I get says "with variable not set"

Sub Change2Text()
Dim Range1 As Range
Dim sValAsString As String

ActiveSheet.Names.Add Name:="Range1", RefersTo:="=" & Selection.Address()
testformat (Range1)

End Sub

Sub testformat(InputRange As Range)
Dim sValAsString As String

With InputRange
sValAsString = CStr(.Value)
.NumberFormat = "@"
.Value = Right("000" & sValAsString, 3)
End With

End Sub

[mikevba]

My latest.... but I get subscript out of range..... I had to use Value2 instead of Value and apparently it is an array

Sub setRange()
Dim Range1 As Range
Dim sValAsString As String

ActiveSheet.Names.Add Name:="Range1", RefersTo:="=" & Selection.Address()
Set Rng = ActiveSheet.Range("Range1")

testformat (Rng)

End Sub

Sub testformat(ByVal InputRange As Range)

Dim sValAsString As String
hi = UBound(InputRange.Value2)
lo = LBound(InputRange.Value2)
For x = lo To hi 'going thru each cell in the range
With InputRange
sValAsString = CStr(.Value2(x)) 'error happens here
.NumberFormat = "@"
.Value = Right("000" & sValAsString, 3)
End With
Next x

End Sub

[DKenny]

Mike
Lets take a step back. I want to make sure we have the requirements correct.
Is the following list correct and complete?
Req 1/ All numeric fields within the selected range should be formated to a fixed 3 character width. (what happens to numbers greater than 999, what happens to decimals?)
Req 2/ All numeric fields should then be formatted as text.

[mikevba]

that is correct, the range will only have integers less than 999

And I want them to be formated as text with leading zeros....

I think it is basically working except for the statement
sValAsString = CStr(.Value) ' problem statement
which gives me a type mismatch error

I tried using.... CStr(.Value(x)) too and that gave me a different error

thanks for the help
Mike

[DKenny]

Mike
Here is a revised version of the Formatting procedure and a demo procedure to show hot to call it. Can you change the reference for the rngOutput variable such that it points to your data range and test the demo proc to see if this is what you need.

Sub MikeFormat(ByRef DataRange As Range)
Dim rngCell As Range
Dim sValAsString As String
    
    For Each rngCell In DataRange
        With rngCell
            If IsNumeric(.Value) Then
                sValAsString = CStr(.Value)
                .NumberFormat = "@"
                .Value = Right("000" & sValAsString, 3)
            End If
        End With
   Next rngCell
End Sub
 
Sub demo()
Dim rngOutput As Range
    
    'You will need to change this line to
    'refer to the range you wish to format
    Set rngOutput = ThisWorkbook.Worksheets(1).Range("A1:H50")
    
    MikeFormat rngOutput
End Sub

[mikevba]

Thanks that works now... it looks like all you did is test for Numeric......

I appreciate the help...... I REALLY do!!!