Solved

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

Posted on 2011-02-17
3
575 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Whether you've completed a degree in computer sciences or you're a self-taught programmer, writing your first lines of code in the real world is always a challenge. Here are some of the most common pitfalls for new programmers.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

939 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now