For Next Loop cell values

grexcelman · #1 01-22-2015, 08:48 AM

I have data in cells A1 through D10 and in column F( F1:F10) I have the averages of each row. I'd like to loop through the range of cells (A1 to D10) and within each row (10 rows) I want to compare the cell value to the corresponding row value in column F(average which is already computed.) Thanks in advance!

I have this so far:

For Each c In Worksheets("Sheet1").Range("a1:d10").Cells

[code to compare c.value to its corresponding F column value]
Next

excelledsoftware · #2 02-11-2015, 11:28 PM

I know this is an older post but I cant help but want to help.

I would write what you are doing slightly different but I know it works this way.

Code:

Sub CompareData()
  'Looks at a range and compares it another
  Dim CheckRow As Integer, ws As Worksheet
  
  Set ws = ThisWorkbook.Worksheets("Sheet1")
  
  For CheckRow = 1 To 10
    If ws.Range("A" & CheckRow).Value = ws.Range("F" & CheckRow).Value Then
      'Do whatever you want to do here.
      'Example 1 ws.range("G" & CheckRow).value = "They Match"
    Else
      'Do whatever you want if they dont match or just remove this to say
      'End if
    End If
  Next CheckRow
  
End Sub

grexcelman · #3 03-03-2015, 10:55 AM

Quote:

Originally Posted by excelledsoftware

I know this is an older post but I cant help but want to help.

I would write what you are doing slightly different but I know it works this way.

Code:

Sub CompareData()
  'Looks at a range and compares it another
  Dim CheckRow As Integer, ws As Worksheet
  
  Set ws = ThisWorkbook.Worksheets("Sheet1")
  
  For CheckRow = 1 To 10
    If ws.Range("A" & CheckRow).Value = ws.Range("F" & CheckRow).Value Then
      'Do whatever you want to do here.
      'Example 1 ws.range("G" & CheckRow).value = "They Match"
    Else
      'Do whatever you want if they dont match or just remove this to say
      'End if
    End If
  Next CheckRow
  
End Sub

This was great. Thanks for replying! I appreciate it as I kept trying to find workarounds for my problem but nothing quite worked as efficiently as I wanted. Your solution should help me. One question, whats the best way to change the sheet name in the 'set ws' line so that I can apply this code on separate sheets?

excelledsoftware · #4 03-03-2015, 02:56 PM

Pretty easy to do actually. Make sure to dim your other worksheets and give them a variable name such as ws and ws1. I will typically use ws for the worksheet that has the data and then rws for the worksheet that gets the results. When you set something you are telling Excel that you want to reference an object. This is different than setting a string = "Your Name Here" which you would do like so.

Code:

TestString = "Your Name Here"

To set a worksheet that is named Data and another worksheet that is named Results refer to the following example.

Code:

  Dim ws as Worksheet, rws as Worksheet

  Set ws = ThisWorkbook.Worksheets("Data")
  Set rws = ThisWorkbook.Worksheets("Results")

You can of course set a variable as a workbook so you dont have to type in ThisWorkbook all the time. This code below should serve as a great reference.

Code:

   Dim ws as Worksheet, rws as Worksheet, wb as Workbook

  Set wb = ThisWorkbook
  Set ws = wb.Worksheets("Data")
  Set rws = wb.Worksheets("Results")

Let me know if you have any other questions.

Thanks

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Convert non-cell input to cell values	TBD	Excel	3	06-09-2014 06:14 AM
Sum values within a single cell	shabbaranks	Excel	17	05-13-2014 06:47 PM
How do you loop through an array with some empty values?	omahadivision	Excel Programming	5	11-28-2013 10:49 AM
change values based on cell	ubns	Excel	1	05-21-2012 06:28 PM
Automatic, Dependent Cell Values	flackend	Excel	2	08-26-2011 07:01 AM