Help fix the code to compare a string with a number

Excelpromax123

Board Regular
Joined
Sep 2, 2021
Messages
172
Office Version
  1. 2010
Platform
  1. Windows
Hi everybody. I need to compare the value of Column B (in red) with the value of Column C. if column B>=Column C, how should the condition be written. Thank you

Code:

VBA Code:
Sub runx()
On Error Resume Next
Dim sArr(), dArr(), I As Long, K As Long, R As Long, Col As Long
sArr = Range("B4:C10").Value
R = UBound(sArr)
ReDim dArr(1 To R, 1 To 2)
For I = 1 To R
     If sArr(I, 1) >= sArr(I, 2) Then '[B]need to fix this code[/B]
        K = K + 1
        For Col = 1 To 2
            dArr(K, Col) = sArr(I, Col)
        Next Col
    End If
Next I
Range("F4:G10").ClearContents
Range("F4").Resize(K, 2) = dArr
End Sub

1693072900524.png
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
How about the following code:

VBA Code:
Sub runx()
'
    Dim ArrayColumn     As Long, ArrayRow           As Long
    Dim ResultRow       As Long
    Dim ResultArray()   As Variant, SourceArray     As Variant
    Dim ws              As Worksheet
'
    Set ws = ThisWorkbook.Sheets("Sheet1")                                              ' <--- Set this to the sheet name to get data from and display results to
'
    SourceArray = ws.Range("B4:C" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row).Value  ' Save the initial source data to SourceArray
'
    ReDim ResultArray(1 To UBound(SourceArray, 1), 1 To UBound(SourceArray, 2))         ' Establish the dimensions of the ResultArray
'
    For ArrayRow = 1 To UBound(SourceArray, 1)                                          ' Loop through the rows of the SourceArray
        If CInt(Mid(SourceArray(ArrayRow, 1), InStrRev(SourceArray(ArrayRow, 1), "-") _
                + 1)) >= SourceArray(ArrayRow, 2) Then                                  '   If value after last '-' in SourceArray(ArrayRow, 1) >= SourceArray(ArrayRow, 2) Then ...
            ResultRow = ResultRow + 1                                                   '       Increment ResultRow
'
            For ArrayColumn = 1 To UBound(SourceArray, 2)                               '       Loop through the columns of the SourceArray
                ResultArray(ResultRow, ArrayColumn) = SourceArray(ArrayRow, ArrayColumn) '          Save SourceArray data into ResultArray
            Next                                                                        '       Loop back
        End If
    Next                                                                                ' Loop back
'
    ws.Range("F4:G" & ws.Range("C" & ws.Rows.Count).End(xlUp).Row).ClearContents        ' Clear previous results
'
    ws.Range("F4").Resize(UBound(ResultArray, 1), UBound(ResultArray, 2)) = ResultArray ' Display the results to the sheet
'
    ws.UsedRange.EntireColumn.AutoFit                                                   ' Autofit the width of the columns
End Sub
 
Upvote 1
What about this?

VBA Code:
Sub runx_v2()
  Dim a As Variant, b As Variant
  Dim i As Long, k As Long
  
  a = Range("B4:C10").Value
  ReDim b(1 To UBound(a), 1 To 2)
  For i = 1 To UBound(a)
    If Val(Split(a(i, 1), "-")(1)) >= a(i, 2) Then
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = a(i, 2)
    End If
  Next i
  Range("F4:G10").ClearContents
  If k > 0 Then Range("F4:G4").Resize(k).Value = b
End Sub
 
Upvote 1
Solution

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top