PDA

View Full Version : [SOLVED] Declared Variable not selecting the correct range



infinity
01-12-2017, 02:40 PM
Hello everyone,

Been a while since I have been here but I have an issue that I am hoping you can help me with. I have declared a variable with the following...





Dim LR As Long
LR = Range("C13:AI13").End(xlDown).Row

Dim R1 As Range
Set R1 = Range("C13:N" & LR)
R1.Select





...which works perfectly to select the entire range, no more and no less. however after I sort this selection I have code that adds a line to the worksheet at row 13, does some formatting, sets some formulas etc. and later I use the "R1.Select" again and it selects the entire range again except it starts at row 14 instead of 13 as the Dim Statement says... URGGGHHH, very frustrating. Can anyone help? Uhhh, stupid question, you all have always been super helpful. Thank you in advance!

snb
01-12-2017, 02:47 PM
Avoid select in VBA.
Remove redundancy.


Sub M_snb()
with cells(13,3)
sn= .resize(.End(xlDown).Row-12,11)
end with
End Sub

infinity
01-12-2017, 10:45 PM
I am not sure if I am missing something but the code you suggested does not do anything.

Paul_Hossler
01-13-2017, 08:28 AM
Not much to go on, but try this




Set R1 = Range("$C$13:$N$" & LR)

infinity
01-14-2017, 11:28 PM
OK, I tried to do something a bit different. This range i was selecting was in order to set a conditional format for the entire range (where the size of the range is always increasing), that was not working the way I expected so I tried something else and instead of selecting the entire range I decided to just select the row I am working on and i used this formula in the Conditional Format dialog box...

=AND(INDIRECT(ADDRESS(ROW(),COLUMN()-2))<>"",INDIRECT(ADDRESS(ROW(),COLUMN()+5))="",MOD(INDIRECT(ADDRESS(ROW(),COLUMN()-2)),2)=0)

I am still going to do this in VBA, and while this does work, it is only formatting cells relative to the active cell. What I need is to select the range C13:N13 and set the format for all of those cells to change font color and/or cell color based on if A13 <>"" and H13="" and whether the value in A13 is an even number or odd number, but I need to be able to use this same formula for Conditional Formatting on all the rows in my worksheet based on the values in that row as i add new rows. I hope this makes sense.

mikerickson
01-15-2017, 10:43 AM
The issue is that R1 is a range object.

At the start, that object starts on row 13.

Then you add a new row to the sheet, moving that object down the sheet to row 14.

R1 is a group of specific cells, whose address might change as the worksheet changes.

infinity
01-15-2017, 08:02 PM
That actually makes sense but I have changed what how I am accomplishing this to adding conditional formats for each line that I add to the sheet. The reason for this is that by setting one conditional format for the entire sheet then adding others for individual ranges is confusing things with my workbook. The formula that I have in my last post does work but it is only working apparently for relative references and I need it to set the format for the entire range selected. Is there any way to make that formula work in conditional formatting with an absolute column but relative row?

infinity
01-15-2017, 09:27 PM
Hey mikerickson, I took another look at your advice above and decided to try it again using your suggestion of changing the declared variable to something other than a range object and it still did the same thing using rangeOne instead of R1. Then I had an epiphany... I have always been in the habit of DIMing my variables at the beginning of my code but with this case I had to move the DIM lower in the code block, to a point after the code adds the line to the worksheet and it selected the range correctly. WOW! That was frustrating but it was a simple solution. Thank you all for your help. Now I am on to see if I can add additional conditional formats to the same range, hopefully it will work, if not I will be back to see if anyone can help. Thank you all as always, you are always so helpful.

Scott

Paul_Hossler
01-16-2017, 07:42 AM
Hey mikerickson, I took another look at your advice above and decided to try it again using your suggestion of changing the declared variable to something other than a range object and it still did the same thing using rangeOne instead of R1. Then I had an epiphany... I have always been in the habit of DIMing my variables at the beginning of my code but with this case I had to move the DIM lower in the code block, to a point after the code adds the line to the worksheet and it selected the range correctly. WOW! That was frustrating but it was a simple solution. Thank you all for your help. Now I am on to see if I can add additional conditional formats to the same range, hopefully it will work, if not I will be back to see if anyone can help. Thank you all as always, you are always so helpful.

Scott

I think it's only necessary to move the 'Set R1 = ...' to after the point where you add the line