CODEWITHSUNDEEP
excelvba
Sergej Dikun
Sergej Dikun

Reputation: 173

VBA Macro Loop Through Columns in Rows and Compare Values

Thank you in advance for taking your time to read through this and helping me. Part of the macro that I am working on should go through rows and compare values in the last two cells of the row (columns will be different for each row thus I can't just take the values in specific two columns to compare).

Later the macro will change values in other cells depending on the finding.

What I am having difficulties with is assigning the value of the last cell in the row and cell before last.

My code is:

Sub compareValues()

Dim allInLastRow As Long
Dim allInLastCol As Long

Dim i As Integer
Dim x As Integer

Dim allInWs As Worksheet

Set allInWs = ThisWorkbook.Worksheets("All In")
allInLastRow = allInWs.Range("C" & Rows.Count).End(xlUp).Row

For i = 2 To allInLastRow 'scans through all the rows
    allInLastCol = allInWs.Cells(i, Columns.Count).End(xlToLeft).Column
    
    For x = 1 To allInLastCol 'scans through all the columns in each row
    
    'in here I need to have the condition that compares lastcell value to 2nd last cell value and this is the one I have a problem with
    
    Next x
Next i


End Sub

Any nudge toward the right direction will be much appreciated. I would love to have an answer from google, but I can't really formulate the question in a way that it would make sense to the search engine :-)

Thank you once again for your help!

Upvotes: 2

Views: 2064

Answers (1)

Davesexcel
Davesexcel

Reputation: 6984

I can think of think of finding last column in row, then finding 2nd last column in row.

Sub LstRw_Stuff()
    
    Dim Rws As Long, r As Range, fRng As Range, col1 As Long, col2 As Long
    Dim rng1 As Range, rng2 As Range
    
    Set r = Range("A1")
    Rws = Cells.Find(What:="*", After:=r, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    For x = 2 To Rws
        col1 = Cells(x, Columns.Count).End(xlToLeft).Column
        Set rng1 = Cells(x, col1)
        s = rng1.Value
        rng1.ClearContents
        col2 = Cells(x, Columns.Count).End(xlToLeft).Column
        Set rng2 = Cells(x, col2)
        rng1 = s
        
        'do whatever need to be done for comparing
        If rng1 > rng2 Then
            MsgBox "yep" & rng1.Value & ">" & rng2.Value
        Else
            MsgBox "Nope" & rng1.Value & "<" & rng2.Value
            
        End If
        
    Next x
End Sub

Upvotes: 2

Related Questions