CODEWITHSUNDEEP
excelvba
Michal Smętek
Michal Smętek

Reputation: 21

Converting Text to Numbers

I am trying to convert text to numbers operating in 2 different worksheets at the same time.

I managed to write something working for a range in a current worksheet:

Sub ConvertTextToNumber1()
  With Range("B5:B14")
      .NumberFormat = "General"
      .Value = .Value
  End With
End Sub

I am trying to convert text to numbers in the below ranges:

string1 = ThisWorkbook.Worksheets("Virt_Summary").Range("A1:DZ50")
string2 = ThisWorkbook.Worksheets("Virt_PPTs").Range("A1:BW50")

Code I tried below:

Sub ConvertTextToNumber()

Dim string1 As Range, string2 As Range
string1 = ThisWorkbook.Worksheets("Virt_Summary").Range("A1:DZ50")
string2 = ThisWorkbook.Worksheets("Virt_PPTs").Range("A1:BW50")

  With Range(string1, string2)
  .NumberFormat = "General"
  .Value = .Value

  End With

End Sub

Do you have any suggestions?

Thanks a lot,

Tried multiple resources and statements.

Upvotes: 1

Views: 126

Answers (1)

Tim Williams
Tim Williams

Reputation: 166126

Add a Range-type parameter to your ConvertTextToNumber sub, and you can call it and pass in different ranges:

Sub Tester()
   With ThisWorkbook
       ConvertTextToNumber .Worksheets("Virt_Summary").Range("A1:DZ50")
       ConvertTextToNumber .Worksheets("Virt_PPTs").Range("A1:BW50")
   End with
End Sub

Sub ConvertTextToNumber(rng As Range)
  With rng
      .NumberFormat = "General"
      .Value = .Value
  End With
End Sub

Upvotes: 1

Related Questions