PDA

View Full Version : Solved: Help adding a couple lines of code



YellowLabPro
06-28-2006, 12:51 PM
This is my existing code. I have three additional procedures that are required, if I could receive some assistance in achieving these three please?

1. The active sheet is retrieving data from two other sheets, the structure is identical, I am using it to create a Pivot Table. I want to populate Column A w/ a fixed name to assist in identifying the data source. For this step use the value of "North Region" for the 1st worksheet's records and "South Region" for the 2nd sheet's records.
Example:
If there were a total of 20 records from sheet 1, then A4:A23 would contain North Region.
If there were 10 records from sheet 2, then A24:A33 would contain "South Region".

2. Remove all interior colors on the worksheet from A4:Down.
3. In two particular columns, I need a fixed value to populate every cell in that range. So if there are 30 total records, beginning in A4, then a fixed value would populate S4:Down. Lets use the value "EOREOR".

I have tried using the recorder for number 2 here, but was unsuccessful.

Thank you in advance for any assistance regarding this post.

YLP

compariniaa
06-28-2006, 12:58 PM
1. The active sheet is retrieving data from two other sheets, the structure is identical, I am using it to create a Pivot Table. I want to populate Column A w/ a fixed name to assist in identifying the data source. For this step use the value of "North Region" for the 1st worksheet's records and "South Region" for the 2nd sheet's records.
Example:
If there were a total of 20 records from sheet 1, then A4:A23 would contain North Region.
If there were 10 records from sheet 2, then A24:A33 would contain "South Region".
Hi, I'm not sure I can help with the first one, but here are the other two:


sub Step2and3 ()
Dim RowNum as Integer

RowNum=Range("A65536").End(xlUp).Row

Range("A4:A"&RowNum).Interior.ColorIndex=xlNone
'this takes care of step 2

Range("S4:S"&RowNum).value="EOREOR"
'this takes care of step 3
End Sub
Here's my shot at step 1:
I'm assuming the values from the different sheets are being put in RangeB4 and down
Before inserting the values from Sheet 2, run this code:
Dim NorthRow as Integer
Dim SouthRow as Integer

NorthRow=Range("B65536").End(xlUp).Row
Range("B4:B"&NorthRow).Value="North Region"

'then the code is run to put in the values from the second sheet

SouthRow=Range("B65536").End(xlUp).Row
Range("B"&NorthRow,"B"&SouthRow).Value="South Region"
End Sub

YellowLabPro
06-28-2006, 03:17 PM
Comparniniaa, thanks for the assistance.
A few things, the procedure for the term EOREOR and Active only fills the first cell/row for its respective column, not down to the last row of the range.
Secondly, the procedure for the interior deals w/ column A only, I need it for the entire sheet.

Thanks for your time and assistance, I will apply the last code you wrote once these others are dialed in.

EDITED********************
I discovered the reason that the term was not filling in Z and AA, was that the RowNum was indexed off of column A, there is nothing in Column A, so using Column B, RowNum = Range("B65536").End(xlUp).Row, resolved that.

The removing color from A4 down and over, still remains unresolved though.

EDITED********************
Again one more discovery, by altering the reference to Range("A4:BB" & RowNum).Interior.ColorIndex = xlNone
and placing it in my last line of code this now has been resolved. However, I would prefer to have the reference not use a fixed range, in this case BB, is there a way to have the procedure look for the last cell in the sheet?

YLP

compariniaa
06-28-2006, 03:52 PM
so you want to remove any color from row 4 down to the last row (for every column)? If so, replace
Range("A4:A"&RowNum).Interior.ColorIndex=xlNone with
Rows("4:" & RowNum).Interior.ColorIndex = xlNone
what do you mean by last cell? do you mean the last cell in column a (or another column) or the last cell in row 1 (or another row), or both, or the cell that is the farthest down regardless of which column? if you want the last cell to be the cell that is farthest down regardless of column, then define RowNum as
RowNum = ActiveSheet.UsedRange.Rows.Count

YellowLabPro
06-29-2006, 02:14 AM
compariniaa,
Thanks, the Rows("4:" & RowNum).Interior.ColorIndex = xlNone , took care of it.
I am still working on #1. I have re-posted it in a new post, complete layout as to avoid unnecessary confusion.
I took your suggestion and purchased VBA for Dummies by Walkenbach last night. I went online and did a search regarding opinons of this text and everyone rated it high, so I am onto to reading it now.

thanks for all your assistance,


cheers,

YLP

compariniaa
06-29-2006, 07:15 AM
I think you'll enjoy it. It's a good introduction. I'd say you're in a pretty good position with VBA for dummies and Power Programming both sitting on your shelf. :)

YellowLabPro
06-29-2006, 07:59 AM
How long have you been at it now?

compariniaa
06-29-2006, 08:41 AM
VBA? since I started my summer job, so that would be june 12. it really helps to be able to dedicate 8 hours a day to learning this stuff :)