using a loop to update a range

jasongrr · Mar 14th 2005

I've been using this website almost religiously to learn the ins and outs of VB...

The issue i have is a two parter: (1st part is what i need help with)
Simply trying to create a loop that will select range "H5:H201" and
make it equal the value of another range (EG5:EG201) (actually on another sheet, even though i haven't id'd the sheet)
Picking only the odd numbers in the range
This would be a lot quicker and easier than typing in each range i want to activate.

Code

Sub ActivateRange()
Application.ScreenUpdating = False
Dim x As Integer
Dim board As Range
Dim from As Range


For x = 5 To x = 201
board = "H" & "x"
from = "EG" & "x"


    ActiveWindow.Panes(1).Activate
    Range(board).Select
   ActiveCell.FormulaR1C1 = Sheets("IMPORT").Range(from).Value
   
   Next
   x = x + 2
   
   End Sub

Display More

The other part is that this is a work around to the ''getting past conditional
formatting 3 limit" code that is displayed on this site.

It is not friendly at all at having a macro copy paste. I have the code do a condition check to turn off the private sub until i'm ready, but the problem is that to have it paint i have to select each cell individually, hence this looping code above to reselect the affected cells so they change to the right color.

Any ideas?

Bill Rockenbach · Mar 14th 2005

Re: using a loop to update a range

Hi there jasongrr,

I think this well do it.

Code

Option Explicit


Sub CopyOddNubmers()
Dim c As Range
Dim TargetSheet As Worksheet


Set TargetSheet = Sheet2  'Put your destination sheet object here
For Each c In Range("EG5:EG201")
    If c Mod 2 <> 1 Then TargetSheet.Cells(c.Row, 8) = c.Value
Next c


End Sub

Display More

Hope this works for you,
Bill

Bill Rockenbach · Mar 14th 2005

Re: using a loop to update a range

For the second part of you question,

What colors are you using for what conditions. There are ways of getting around the 3 condition limit of conditional formating.

Bill

jasongrr · Mar 15th 2005

Re: using a loop to update a range

I realize I may not have been the clearest - I don't need it to just equal the odd # range, I need it to activate the cell so that the conditional formatting will activate.

Also, I haven't had success in utilizing the code that was given. The last line kicks out an error

Code

If c Mod 2 <> 1 Then TargetSheet.Cells(c.Row, 8) = c.Value Next c

This is what i used a macro to get to activate the cell, maybe it will speak better than i -

Code

Range("H7").Select
   ActiveCell.FormulaR1C1 = Sheets("IMPORT").Range("EG7").Value

I'm trying to avoid going down the entire row typing in, H9.... EG9... etc.
until I go to 201.

I know i'm just missing a tiny piece of something out of your code, but honestly not sure what. (my "import" sheet just happens to be sheet2, so that was helpful, my target sheet is of course sheet 1)

jasongrr · Mar 15th 2005

Re: using a loop to update a range

more ways than the one presented on this site?

Bill Rockenbach · Mar 15th 2005

Re: using a loop to update a range

My code assumes that it is placed in the module associated with the Source sheet (the sheet with all the odd and even numbers) which is active. It copies all even numbers to the Target sheet (Sheet2 in the example). I tested it on xl2000 /windows xp.

What is the error message you get.

Bill

jasongrr · Mar 15th 2005

Re: using a loop to update a range

syntax error for line

Code

If c Mod 2 <> 1 Then TargetSheet.Cells(c.Row, 8) = c.Value Next c

Bill Rockenbach · Mar 15th 2005

Re: using a loop to update a range

OK IS there an erro message and error message number

Bill Rockenbach · Mar 15th 2005

Re: using a loop to update a range

One way to get around the conditional formating limitation of three is to use the Private Sub Worksheet_Change(ByVal Target As Range) event in combination with the intersect function in palce of or in combination with conditional formating. This will give you unlimited conditions. Check out Dave Hawley's 100 Hacks, Chapter 7. You can find it o this site.
Bill

tinyjack · Mar 15th 2005

Re: using a loop to update a range

It is 2 lines of code as in the original post!

Code

If c Mod 2 <> 1 Then TargetSheet.Cells(c.Row, 8) = c.Value
Next c

TJ

Bill Rockenbach · Mar 15th 2005

Re: using a loop to update a range

Thanks TJ. My mind is on a two week vacation. I'm out of here in one hour.

jasongrr · Mar 15th 2005

Re: using a loop to update a range

have a good one, thanks for the help, I think i can work out some of the other issues:
ie next without a for condition (for c=5 to c=201)

Thanks again.

jasongrr · Mar 15th 2005

Re: using a loop to update a range

DISREGARD previous..... saw the For statement.... by formatting really threw me for a loop!
hA!

SilverGlyde · May 17th 2005

defining ranges for match function

First time board user, so I hope I've put this query in the right place...and hope I can find my way back here to see replies! TIA...

Code

' This works:
Ans = Application.WorksheetFunction.Match(ShapeName, Worksheets("Data").Range("C2:G2"), 0)
'This doesn't:
Ans = Application.WorksheetFunction.Match(ShapeName, Worksheets("Data").Range(Cells(2, 3), Cells(2, 7)), 0)

One works, one doesn't (returns nothing) - why not?

using a loop to update a range

Participate now!

Share