Results 1 to 5 of 5

Thread: VBA To Compare Multiple Cells

  1. #1
    Junior Member
    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0

    VBA To Compare Multiple Cells

    Hi everyone,

    I am new to VBA programming in Excel. Can someone please help me create a VBA program. I have searched in multiple websites but i couldn't find anything. I already made the program to compare 2 columns based of fixed rows and cells.

    What i'm trying to do now is the following:

    - The user will input the number of columns and select it's cell location
    - Then the macro will compare the cell values of every columns at the same time, row by row. For example: Cell 1, Column 1 vs Cell 1, Column 2 and Cell 1, Column 1 vs Cell 1, Column 3 (if the number of selected columns is 3)
    - If the compare fails the script will shift the row of the column where the compare failed down.

    Here's what i made so far, the problem is the columns comparision is made only by 2 columns at a time. I've also attached my workbook.

    13081715551.xlsm

    Code:
    Option Base 1
    Sub userDef()
        Dim cNum As Long
        Dim stColArray(), lstColArray() As Variant
        Dim cRowArray(), cColArray() As Variant
        Dim cSel, rSel As Range
        Dim rw, col, j, mvSt1, mvEnd1, mvSt2, mvEnd2 As Integer
    
        j = 1
        
        cNum = Application.InputBox("Number of columns:")
        ReDim rStArray(cNum)
        ReDim stColArray(cNum)
        ReDim lstColArray(cNum)
        ReDim cRowArray(cNum)
        ReDim cColArray(cNum)
        For c = 1 To cNum
            Set rSel = Application.InputBox("Select " & c & " table(s)", Type:=8)
            Set cSel = Application.InputBox("Select " & c & " cell(s) of " & c & " table", Type:=8)
            If rSel Is Nothing Then
                MsgBox "No cell selected"
                Exit Sub
            Else
                stColArray(j) = rSel.Column
                lstColArray(j) = rSel.Columns(rSel.Columns.Count).Column
                cRowArray(j) = cSel.Row
                cColArray(j) = cSel.Column
                j = j + 1
            End If
        Next c
        
        
        j = 1
        x = 1
        y = 2
        rw = cRowArray(j)
        
    nxtChk:
      
      a = Math.Round(Cells(rw, cColArray(x)).Value, 2)
      d = Math.Round(Cells(rw + 1, cColArray(x)).Value, 2)
      mvSt1 = stColArray(x)
      mvEnd1 = lstColArray(x)
    
      b = Math.Round(Cells(rw, cColArray(y)).Value, 2)
      c = Math.Round(Cells(rw + 1, cColArray(y)).Value, 2)
      mvSt2 = stColArray(y)
      mvEnd2 = lstColArray(y)
      
      stDevAB = Math.Sqr((((b - ((b + a) / 2)) ^ 2) + ((a - ((b + a) / 2)) ^ 2)) / 2)
      stDevAC = Math.Sqr((((c - ((c + a) / 2)) ^ 2) + ((a - ((c + a) / 2)) ^ 2)) / 2)
      stDevBA = Math.Sqr((((a - ((a + b) / 2)) ^ 2) + ((b - ((a + b) / 2)) ^ 2)) / 2)
      stDevBD = Math.Sqr((((d - ((d + b) / 2)) ^ 2) + ((d - ((d + b) / 2)) ^ 2)) / 2)
      stErrAB = stDevAB / Math.Sqr(2)
      stErrAC = stDevAC / Math.Sqr(2)
      stErrBA = stDevBA / Math.Sqr(2)
      stErrBD = stDevBD / Math.Sqr(2)
      
      If a > 0 And b > 0 Then
        chisqrAB = ((b - a) - 0.05) ^ 2 / a
        p_val_AB = WorksheetFunction.ChiDist(chisqrAB, 1)
        chisqrAC = ((c - a) - 0.05) ^ 2 / a
        p_val_AC = WorksheetFunction.ChiDist(chisqrAC, 1)
        chisqrBA = ((a - b) - 0.05) ^ 2 / b
        p_val_BA = WorksheetFunction.ChiDist(chisqrBA, 1)
        chisqrBD = ((d - b) - 0.05) ^ 2 / b
        p_val_BD = WorksheetFunction.ChiDist(chisqrBD, 1)
      End If
     
      If a > 0 And stDevAB > stDevAC And stErrAB > stErrAC And p_val_AB < p_val_AC Then
            For col = mvSt1 To mvEnd1
            Cells(rw, col).Insert shift:=xlDown
            Next col
      ElseIf b > 0 And stDevBA > stDevBD And stErrBA > stErrBD And p_val_BA < p_val_BD Then
            For col = mvSt2 To mvEnd2
            Cells(rw, col).Insert shift:=xlDown
            Next col
      End If
    
    
        If rw > 5 And b = 0 Then
        y = y + 1
        rw = cRowArray(j) - 1
        End If
        If rw > 5 And b = 0 And y > cNum Then Exit Sub
        rw = rw + 1
    
    GoTo nxtChk
        
    End Sub
    I'm stuck at this point.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    x010, I see three separate sets of data in your worksheet. Can you show exactly what you are trying to do?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    I'm trying to compare the last cell of the first data set with the last cell of the second data set and the last cell of the first data set with the last cell of the last data set. I'm trying to make this comparison at the same time because the purpose of the macro is to format the sets of data inserting blank rows.

    I'm calculating the standard deviation and p-values and using that to compare each cell.

    If i have not made myself very clear please let me know as i will try to explain a little better.

    Thank you.


    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.youtube.com/watch?v=oVb1RfcSHLM&lc=UgwTq-jZlZLnLQ5VB8Z4AaABAg.9Hroz-OyWog9tYjSMc1qjA
    https://www.youtube.com/watch?v=0pbsf6sox34&lc=Ugxp9JFvvejnqA68W1t4AaABAg
    https://www.youtube.com/watch?v=kfQC-sQxMcw&lc=UgyCxQWypNIhG2nUn794AaABAg.9q1p6q7ah839t UQl_92mvg
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgyOh-eR43LvlIJLG5p4AaABAg.9isnKJoRfbL9itPC-4uckb
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugy1B1aQnHq2WbbucmR4AaABAg. 9isY3Ezhx4j9itQLuif26T
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgxxajSt03TX1wxh3IJ4AaABAg. 9irSL7x4Moh9itTRqL7dQh
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=Ugxa2VYHMWJWXA6QI294AaABAg. 9irLgSdeU3r9itU7zdnWHw
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgwJAAPbp8dhkW2X1Uh4AaABAg. 9iraombnLDb9itV80HDpXc
    https://www.youtube.com/watch?v=vXyMScSbhk4&lc=UgzIzQ6MQ5kTpuLbIuB4AaABAg. 9is0FSoF2Wi9itWKEvGSSq
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK75iCEaGN
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iK7XF33njy
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCSgpAqA1
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iKCy--3x8E
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwNaJiNATXshvJ0Zz94AaABAg. 9iEktVkTAHk9iF9_pdshr6
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgykemWTw-fGoPwu8E14AaABAg.9iECYNx-n4U9iFAZq-JEZ-
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgxV2r7KQnuAyZVLHH54AaABAg. 9iDVgy6wzct9iFBxma9zXI
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugx12mI-a39T41NaZ8F4AaABAg.9iDQqIP56NV9iFD0AkeeJG
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwnYuSngiuYaUhEMWN4AaABAg. 9iDQN7TORHv9iFGQQ5z_3f
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwJ3yzdk_EE98dndmt4AaABAg. 9iDLC2uEPRW9iFGvgk11nH
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgyDWAVqCa4yMot463x4AaABAg. 9iH3wvUZj3n9iHnpOxOeXa
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=UgwvLFdMEAba5rLHIz94AaABAg. 9iGReNGzP4v9iHoeaCpTG8
    https://www.youtube.com/watch?v=ITI1HaFeq_g&lc=Ugy_1xkcndYdzUapw-J4AaABAg.9iGOq_leF_E9iHpsWCdJ5I
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 09-13-2023 at 10:30 AM.

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    Still not very clear. Can you post a sample workbook with the original input, and the expected output after the macro is run? And then explain what was the intended logic. Probably that should make it clear.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Aug 2013
    Posts
    3
    Rep Power
    0
    If you take the my first uploaded workbook as my first input, here's what the output workbook should look like: 13081715551-out.xlsb

    I've trimmed it down because there were too much values to correct manually.

    Hope this helps.

Similar Threads

  1. Replies: 9
    Last Post: 08-23-2013, 04:25 PM
  2. Replies: 4
    Last Post: 08-20-2013, 06:28 PM
  3. VBA Program to Compare 4 Columns in Excel (Required)
    By vijaysram in forum Excel Help
    Replies: 11
    Last Post: 06-26-2013, 10:53 AM
  4. Replies: 13
    Last Post: 06-10-2013, 09:05 AM
  5. Replies: 3
    Last Post: 05-23-2013, 11:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •