?
Solved

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

Posted on 2011-02-17
3
Medium Priority
?
583 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 1500 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: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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