Solved

VBA Code Needed - Compare Cells in a Column to Cells in Previous Column

Posted on 2011-02-17
3
577 Views
Last Modified: 2012-05-11
I have what seems to be a very simple request. I need to know how the VBA code would look if I wanted to compare values of cells in a column to values of cells in another column using the offset property.

For example.

A     B     C     D
1     2      3      4
2     3      4      5
3     4      5      6
4     5      6      7

I want to go down each column, starting with B, and compare it to the cell's offset of 1 column over (-1).  This comparison would obviously need to be 1 cell at a time, but needs to be relative so that I can run this macro on any column. Preferably this would go down the entire column until it reaches null cell.
0
Comment
Question by:ChuckDeezel
3 Comments
 
LVL 10

Accepted Solution

by:
PSSUser earned 500 total points
ID: 34919114
If you want to work using column letters as opposed to numbers the code is

Dim ColumnLetter As String, StartRow As Integer
Dim RowCnt As Long

RowCnt = 0

'Below I've hard coded column and start row.
'But use whatever code you wish to return a column letter and row
ColumnLetter = "B"         'No data to the left of Column A so start with B
StartRow=2                    'I'm assuming row 1 is column headers

With Range(ColumnLetter & CStr(StartRow))
  Do Until .Offset(RowCnt).Value = ""
    'Compare Code here e.g

    'Since comparing to column to the left if the chosen column was A you would get an error
    'So only compare if the selected column isn't A.
    If ColumnLetter <> "A" Then
      If .Offset(RowCnt, -1).Value < .Offset(RowCnt, 0).Value Then
        'Some Code
      Else
        'Alternative Code
      End If
    End If
    RowCnt = RowCnt + 1
  Loop
End With

Open in new window

0
 
LVL 22

Expert Comment

by:rspahitz
ID: 34919168
My first question is: are you trying to learn how to use the Offset function, or are you trying to solve a problem?

To solve this problem, simply insert a new column between B and C and use the formula in C1:

=IF(A1=B1,"match", "no match")

Copy this formula down as far as needed.  no matter where you copy it, it will be the two cells to the left.

--
For a more VBA approach, I recommend turning on Relative Referencing when you record a macro.  To set that, go to menu tab View | Macros | Use Relative References.

When you complete your macro, the code will show you how VBA uses the Offset command.
0
 
LVL 4

Author Closing Comment

by:ChuckDeezel
ID: 34921469
Thanks, this has put me on the right track.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question