check if isnumeric data in two different cell in vba

[mehidy1437]

Hi
How can I check, if cell a1 or cell b1 contain numerics data/value or not in vba?

 

[KOKOSEK]

If IsNumeric(Sheet1.Range("A1").Value) = True Then MsgBox "The value in A1 is numeric"

 

[mehidy1437]

Sorry my question was a bit confusing, i believe.

It should be, if cell a1 or cell e1 is numeric or not? I don't want check others cell in between a1 & e1.
Hope its clear now.

 

[KOKOSEK]

If IsNumeric(Sheet1.Range("A1").Value) = True or IsNumeric(Sheet1.Range("E1").Value) = True Then MsgBox "A1 or E1 is numeric."

 

[mehidy1437]

Thanks for your help.
Is it necessary to mention the Sheet name for IsNumeric? Actually I'm trying to avoid sheet name. If possible.

 

[mehidy1437]

This is giving me an error 5: for IsNumeric.

Option Explicit

Sub CalculationOnSelctionRng()
  
   'Set up the variables.
    Dim Rng As Range
    Dim selectioncell As String
    Dim firstcolcellad As String
    Dim lastcolrowad As String
  
    Dim firstcolcell As Double
    Dim lastcolrow As Double
    Dim mUlitipleValue As Double
    Dim sUbtractValue As Double
    Dim sUmValue As Double
    Dim dVidedValue As Double
  
   Set Rng = Selection
  
   selectioncell = Rng.Address(0, 0)
  
     firstcolcellad = ActiveCell.Address(0, 0)
        lastcolrowad = Rng(Rng.count).Address(0, 0)
 If IsNumeric(Rng(" & firstcolcellad & ").Value) = True Or IsNumeric(Rng(" & lastcolrowad & ").Value) = True Then
 
    MsgBox "The value in RANGE is numeric"
 
Else

     firstcolcell = ActiveCell.Value
     lastcolrow = Rng(Rng.count).Value

        mUlitipleValue = firstcolcell * lastcolrow
        sUbtractValue = firstcolcell - lastcolrow
        sUmValue = firstcolcell + lastcolrow
        dVidedValue = firstcolcell / lastcolrow
    
    MsgBox "Cell =" & selectioncell & vbNewLine & _
    " " & vbNewLine & _
    "Subtract =" & sUbtractValue & vbNewLine & _
     "Multiple =" & mUlitipleValue & vbNewLine & _
     "Divide =" & dVidedValue & vbNewLine & _
     "Sum =" & sUmValue & vbNewLine
     End If
End Sub

 

[mehidy1437]

Got the solution as below.

  Option Explicit

Sub CalculationOnSelctionRng()
  
   'Set up the variables.
    Dim Rng As Range
    Dim selectioncell As String
    Dim firstcolcellad As String
    Dim lastcolrowad As String
  
    Dim firstcolcell As Variant
    Dim lastcolrow As Variant

    Dim mUlitipleValue As Double
    Dim sUbtractValue As Double
    Dim sUmValue As Double
    Dim dVidedValue As Double
  
   Set Rng = Selection
  
   selectioncell = Rng.Address(0, 0)
  
     firstcolcellad = ActiveCell.Address(0, 0)
        lastcolrowad = Rng(Rng.count).Address(0, 0)
          firstcolcell = ActiveCell.Value
                lastcolrow = Rng(Rng.count).Value
 'If WorksheetFunction.IsNumber(firstcolcell)= True And WorksheetFunction.IsNumber(lastcolrow) = True Then
 If WorksheetFunction.IsNumber(firstcolcell) And WorksheetFunction.IsNumber(lastcolrow) = True Then
        
        mUlitipleValue = firstcolcell * lastcolrow
        sUbtractValue = firstcolcell - lastcolrow
        sUmValue = firstcolcell + lastcolrow
        dVidedValue = firstcolcell / lastcolrow
    
    MsgBox "Cell =" & selectioncell & vbNewLine & _
    " " & vbNewLine & _
    "Subtract =" & sUbtractValue & vbNewLine & _
     "Multiple =" & mUlitipleValue & vbNewLine & _
     "Divide =" & dVidedValue & vbNewLine & _
     "Sum =" & sUmValue & vbNewLine
 
 
Else
    MsgBox "The values in SELECTION is not number"

     End If
End Sub

 

[DanteAmor]

Sometimes it is not necessary to have many variables, you can directly perform the operations, for example:

Sub CalculationOnSelctionRng_2()
  Dim Rng As Range, fCell As Variant, lCell As Variant
  Set Rng = Selection
  fCell = Rng.Cells(1).Value
  lCell = Rng(Rng.Count).Value
  If IsNumeric(fCell) And IsNumeric(lCell) = True Then
    MsgBox "Cell    " & vbTab & "= " & Rng.Address(0, 0) & vbCr & vbCr & _
           "Subtract" & vbTab & "= " & fCell - lCell & vbCr & _
           "Multiple" & vbTab & "= " & fCell * lCell & vbCr & _
           "Divide  " & vbTab & "= " & fCell / lCell & vbCr & _
           "Sum     " & vbTab & "= " & fCell + lCell & vbCr
  Else
    MsgBox "The values in SELECTION is not number"
  End If
End Sub

 

[Rick Rothstein]

Here is another way to write the macro...

Sub CalculationOnSelctionRng_4()
  Dim X As Long, ArrSymbols As Variant, ArrWords As Variant
  ArrSymbols = Array(, , "-", "*", "/", "+")
  ArrWords = Array("Cell:" & vbTab & Selection.Address(0, 0), "", "Subtract:", "Multiply:", "Divide:", "Sum:")
  For X = LBound(ArrSymbols) + 2 To UBound(ArrSymbols)
    ArrWords(X) = ArrWords(X) & vbTab & Evaluate(Replace(Selection.Address(0, 0), ":", ArrSymbols(X)))
  Next
  MsgBox Join(ArrWords, vbLf)
End Sub

 

[DanteAmor]

Here is another way to write the macro...

Hi Rick, it is certainly another way to write the macro, but it does not solve the initial OP question "check if isnumeric data in two different cell in vba".
If the cells are not numeric, your macro sends an error: "Type Mismatch"