Excel Macros: Comparing cells in two sheets

Compare and replace certain cells

Page 1 of 1

12 Replies - 10058 Views - Last Post: 24 June 2009 - 01:44 PM Rate Topic: -----

#1 meatloaf   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 65
  • Joined: 05-September 07

Excel Macros: Comparing cells in two sheets

Posted 11 June 2009 - 01:51 PM

I am completely clueless to Visual Basic, Im a Java, C++ person.

Situation:
Layout sheet, column A has stock symbols (ie "GE") occuring once, column C has new price (ie "11.44")
List sheet, column D has stock symbols occuring multiple times, column F and G are old price

What I want to do:
Compare stock symbols on each row from Layout:A against List:D
if the symbols match, replace List:G(old price) with Layout:C(new price)
else replace List:G with ""
Then I want to compare List:G(now new price) with List:F(old price)
if List:G is greater than List:F, bold the row
Finally I want to take the first instance of each stock symbol in List:D and highlight it green

Here is my sad,confusing,unworkable code:
Sub List1()
'
' List1 Macro
' Stop loss orders List
'

' Declare and initialize variables for row, size(# of rows)
	Dim row, column, size As Integer
	row = 1
	column = 1
	size = Range("G1", ActiveSheet.Range("G1").End(xlDown)).Cells.Count
	
' Copy data from column F(Price) and insert copied cells into new column G
	ActiveCell.Offset(0, 5).Columns("A:A").EntireColumn.Select
	Selection.Copy
	Selection.Insert Shift:=xlToRight
	Application.CutCopyMode = False
	
' Compare equiv cells of Symbol(List D:Layout A),
'   if equal,	 replace List G with Layout C (price)
'   if not equal, replace List G with ""
	'For row = 1 To size
	 '   Cells(row, C).Select
	  '  Range("Symbol").Cells(row) = 1
	'
	'	   If ActiveCell.Offset(0, -3).Range("C1") Then
	 '
	 '	 End If
	'Next row
	
	Dim x As Integer
	x = lastcell.row + 1
	Rows("5:" & x).Select
	
	' highlight differences/greater thans in sheets
	For row = 1 To size
		If Sheets("List").Cells(row, Columns("D:D")).Value = Sheets("Layout").Cells(row, Columns("A:A")).Value Then
			' replace List G# with Layout C# (price)
			Sheets("List").Cells(row, Columns("G:G")).Value = Sheets("Layout").Cells(row, Columns("C:C")).Value
			If Sheets("List").Cells(row, Columns("G:G")).Value > Sheets("List").Cells(row, Columns("D:D")).Value Then
				Sheets("List").Cells(row, Columns("G:G")).Interior.ColorIndex = 6
			End If
		Else
			Sheets("List").Cells(row, column).Interior.ColorIndex = xlNone
		End If
	Next row
	
	
  '  Set NewSheet = Sheets.Add(Type:=xlWorksheet)
  'For i = 1 To Sheets.Count
  'NewSheet.Cells(i, 1).Value = Sheets(i).Name
  'Next i

	' =Sheet1!$A$1:$A$14
	
   ' For row = 1 To size
   '	 Range("Target").Cells(row).GoalSeek Goal:=1, _
   '		 ChangingCell:=Range("Symbol").Cells(row)
   ' Next row

	
' If List G <> "", highlight the row
	
' Set active cell to A1
	ActiveCell.Offset(0, -6).Range("A1").Select
End Sub



Thanks in advance!

Is This A Good Question/Topic? 0
  • +

Replies To: Excel Macros: Comparing cells in two sheets

#2 June7   User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Excel Macros: Comparing cells in two sheets

Posted 11 June 2009 - 06:43 PM

Why is it unworkable? Error messages, wrong results, nothing happens? Have you step debugged? What line fails? Actually looks like a good start at first glance. I might try to recreate and analyze this weekend if no solution posted by then.
Was This Post Helpful? 0
  • +
  • -

#3 meatloaf   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 65
  • Joined: 05-September 07

Re: Excel Macros: Comparing cells in two sheets

Posted 12 June 2009 - 11:08 AM

View PostJune7, on 11 Jun, 2009 - 05:43 PM, said:

Why is it unworkable? Error messages, wrong results, nothing happens? Have you step debugged? What line fails? Actually looks like a good start at first glance. I might try to recreate and analyze this weekend if no solution posted by then.


Debugging, the second line in the for loop it has issues with
If Sheets("List").Cells(row, Columns("D:D")).Value = Sheets("Layout").Cells(row, Columns("A:A")).Value Then


and I haven't been able to get much beyond that.

And thanks. As I said, Im clueless about VB and this code is a conglomeration of code I found online that looks as if it may work...hence all the random code commented; and it not working.
Was This Post Helpful? 0
  • +
  • -

#4 June7   User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Excel Macros: Comparing cells in two sheets

Posted 12 June 2009 - 08:32 PM

Thanks for identifying the problem line, next time include the error message as well. The more clues the better. I also stumbled on x = lastcell.row + 1 until realized 'lastcell' must be a user function not included in your code post. I set x to a number to get past this line.

Doesn't like any of the Columns("%:%"). When I replace all of them with index number, it runs until the last line (ActiveCell...) and bombs on it. Why not simply Range("A1").Select (worked for me)? Ran through without error, didn't try to figure out if the results are correct.

This post has been edited by June7: 12 June 2009 - 08:43 PM

Was This Post Helpful? 0
  • +
  • -

#5 meatloaf   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 65
  • Joined: 05-September 07

Re: Excel Macros: Comparing cells in two sheets

Posted 15 June 2009 - 10:28 AM

I changed the Columns- to Range- as you suggested. It compiles but does not run. Still bombs on the same line of code.

Quote

Run-time error '9': Subscript out of range.

Was This Post Helpful? 0
  • +
  • -

#6 June7   User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Excel Macros: Comparing cells in two sheets

Posted 15 June 2009 - 11:47 AM

My suggestion was not to change columns to range but to replace 'Columns("%:%")' with the index number for the column. Example for column D:
Sheets("List").Cells(row, 4).Value

And change 'ActiveCell.Offset(0, -6).Range("A1").Select' to:
Range("A1").Select
These edits worked for my testing.

This post has been edited by June7: 15 June 2009 - 11:47 AM

Was This Post Helpful? 0
  • +
  • -

#7 meatloaf   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 65
  • Joined: 05-September 07

Re: Excel Macros: Comparing cells in two sheets

Posted 15 June 2009 - 12:06 PM

' Compare equiv cells of Symbol(List D:Layout A),
'   if equal,	 replace List G with Layout C (price)
'   if not equal, replace List G with ""
	'For row = 1 To size
	 '   Cells(row, C).Select
	  '  Range("Symbol").Cells(row) = 1
	'
	'	   If ActiveCell.Offset(0, -3).Range("C1") Then
	 '
	 '	 End If
	'Next row
	
	'Dim A, C, D, G As Range
	'A = Range("A:A")
	'C = Range("C:C")
	'D = Range("D:D")
	'G = Range("G:G")
	Dim Layout As Sheet1
	Dim List As Sheet2
	Dim x As Integer
	x = 1
	'x = lastcell.row + 1
	Rows("5:" & x).Select
	
	' highlight differences/greater thans in sheets
	For row = 1 To size
		If Sheets(List).Cells(row, 4).Value = Sheets(Layout).Cells(row, 1).Value Then
			' replace List G# with Layout C# (price)
			Sheets(List).Cells(row, 7).Value = Sheets(Layout).Cells(row, 3).Value
			If Sheets(List).Cells(row, 7).Value > Sheets(List).Cells(row, 4).Value Then
				Sheets(List).Cells(row, 7).Interior.ColorIndex = 6
			End If
		Else
			Sheets(List).Cells(row, column).Interior.ColorIndex = xlNone
		End If
	Next row



When I change If Sheets("List") to If Sheets(List), the error becomes:

Quote

Runtime error '13': Type mismatch

and when Sheets("List"), it's still:

Quote

Runtime error '9': Subscript out of range


I think the Sheets is the problem, not Cells...
Was This Post Helpful? 0
  • +
  • -

#8 GMorris   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 4
  • Joined: 15-June 09

Re: Excel Macros: Comparing cells in two sheets

Posted 15 June 2009 - 12:28 PM

Hello everyone,

I'm brand-new here, but to me this looks like an attempt at
"salvaging" a recorded macro or something. If not, it sure
does look like it. What I would do is just start over, and I'll
usually at least study a recorded macro if I can to determine
what needs to be done. Lots of times, the macro recorder
can give you lots of insights into what code to use, but it
tends to take things a little TOO literally sometimes. Like
using an entire row or column for something that you only
need for part of a range or even just one cell! If you already
have programming experience in one of the OO languages,
VB should be anything but scary. It's different, I'll give it that
no doubt, but once you figure out where everything is it's a
breeze. One of the best things about VBA for Office is the
massive amounts of documentation for it, you can find out
how to do something in no time.

Good Luck!
Was This Post Helpful? 0
  • +
  • -

#9 June7   User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Excel Macros: Comparing cells in two sheets

Posted 17 June 2009 - 09:59 AM

Do not remove the quote marks from Sheets("List"). I did find the cursor had to start in a cell to the left of the columns referenced in the code. So I positioned it in A1 and then executed. As I said, with the edits I made it ran without problem. So nothing wrong with the Sheets reference.
Do you have Microsoft Excel X.0 Object Library selected in the VBA Code Editor References list?
Was This Post Helpful? 0
  • +
  • -

#10 meatloaf   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 65
  • Joined: 05-September 07

Re: Excel Macros: Comparing cells in two sheets

Posted 18 June 2009 - 01:18 PM

Quote

this looks like an attempt at
"salvaging" a recorded macro or something

no, it's a conglomeration of code i was finding online.

Quote

Do you have Microsoft Excel X.0 Object Library selected in the VBA Code Editor References list?

yes, i have Microsoft Excel 12.0 Object Library already selected.

i changed the code back to what you said and it still gave that same error!
Was This Post Helpful? 0
  • +
  • -

#11 June7   User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Excel Macros: Comparing cells in two sheets

Posted 21 June 2009 - 03:49 PM

If you want to download the file to this site http://www.box.net/shared/0mfqudj3nz
I will take a look at.
Was This Post Helpful? 0
  • +
  • -

#12 June7   User is offline

  • D.I.C Addict
  • member icon

Reputation: 104
  • View blog
  • Posts: 904
  • Joined: 09-December 08

Re: Excel Macros: Comparing cells in two sheets

Posted 23 June 2009 - 07:23 PM

I have modified the workbook and placed in the same location:
http://www.box.net/shared/v9mfayh9s3
I have comments in the workbook on a sheet for you to review.
Was This Post Helpful? 0
  • +
  • -

#13 meatloaf   User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 65
  • Joined: 05-September 07

Re: Excel Macros: Comparing cells in two sheets

Posted 24 June 2009 - 01:44 PM

To June7:
Thank you so much!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1