Solved

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

Posted on 2011-02-17
3
578 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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 make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

762 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