VBA Macro Loop Through Columns in Rows and Compare Values

Question

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!

Would it be safe to say, each columns last row is different ? So you are looping through each column finding the last cell in that column and comparing it with the cell above? — Davesexcel
– Davesexcel, Commented Jun 29, 2020 at 12:21
It's about 300 rows and for some of the columns you would have gaps row wise so wont work that way unfortunately. Example would be: Row 1 has 10 different values in 10 columns, Row 2 has 20 values in 20 columns, Row 3 15 values in 15 columns Row 4 30 Values in 30 Columns etc. So if I scan column 16, then it would stop straight away as values for row 1 is empty. At the same time amount of rows is changing so while right now it's 300, next day it can be 400 or more. — Sergej Dikun
– Sergej Dikun, Commented Jun 29, 2020 at 12:32
Ah, yes I had read that originally, then somehow my mind went to last row instead of last column.. So for example: in row 1 the last column is "M" but possibly the 2nd last column is "H"? — Davesexcel
– Davesexcel, Commented Jun 29, 2020 at 12:37

Davesexcel · Accepted Answer · 2020-06-29 13:14:02Z

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

Exactly what I was looking for! My main issue was assigning the value to the variable and finding the value of the 2nd last column, just wouldn't want to work for me. Thanks a mil!

Collectives™ on Stack Overflow

VBA Macro Loop Through Columns in Rows and Compare Values

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related