Solved

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

Posted on 2011-02-17
3
579 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

732 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