CELL REFERENCE IN VBA

[tyone]

what i am trying to do is look thru a spreadsheet by columns and find a value. if that value is found then i need to know what the cell refrence is so that i can add a row beneth it so i can do subtotals under it. the find works but i don't know what the statement is for cell refrence. i know =address(rows(),columns()) would work in a formula.

thanks in advance

 

[CBasicAsslember]

Post the code you have.

 

[tyone]

cbasic i don't have any code for that as of yet.
what i tried was to dim a variable as string and put that in front of the formula but then it comes up with a compile error. i am not sure what to use for the code. i do all most of my programming in rpg so i am lost as to programming in vba. the part that i am using for the find is from recording a macro to find it and then i dropped that into a sub() on the sheet. not sure if that is really the way to it either.

 

[SkipVought]

Hi,

Have you looked at the Data/Subtotal feature in Excel? It does just what you want to do in mere SECONDS!

Skip,

_[red][/red]

 

[tyone]

hi skip yes i have looked into that but this file at the present time is getting close to 5000 rows of data. so i want to find a value then automaticlly insert a line and yes i will then use the data/sub totals part of it.. yes i understad that i could also use the find feature from excel also. just wanted it a little more automatic. is all

 

[SkipVought]

"...just wanted it a little more automatic..."

How much more automatic than the Subtotal Feature. You can do 50,000 rows in SECONDS with half a dozen clicks. Record a macro that does the steps.

Skip,

_[red][/red]

 

[CBasicAsslember]

tyone,

Did you look at Find in help? The example should help you with the address.

 

[corycrum]

I'm trying to do the same thing. I have 300 tabs to calculate subtotals on and about 1,000 rows of data on each tab. I have a macro that will calculate subtotals on the first tab and then switch to the next, calculate subtotals on it and so on, through all 300 tabs.

here is the subtotals code that I'm using:

Selection.Subtotal GroupBy:=?1, Function:=xlSum, TotalList:=Array(?2), SummaryBelowData:=True, Replace:=True

Where ?1 is the column that you want to base the subtotals on and ?2 represents the column of data that you want to sum up.

For example, if you columns are:


Date Account# Vendor Amount


and you want to calculate subtotals for "Amount" at each change in "Account#", then ?1 would be 2, and ?2 would be 4.

Make sense?

The problem that I'm having with subtotals is that the format that Excel applies to them is ugly. I'm sending these out as reports to executives and I would like to "dress them up" a little to make them more visually appealing. I need some code that will, after calculating the subtotals, insert another 2 blank rows below each subtotal line to further separate one group from another. Additionally, I need each subtotal line to be in bold and highlighted in light yellow.

The macro needs to look through each row, and if a row has subtotals on it, insert a few rows below it and apply the formats. How can I tell Excel to "identify" rows with subtotals so that it can make the changes?

I think this is in line with what Tyone is trying to accomplish.

I hope this helps and if someone can advise me I would really appreciate it.

Thanks,

Cory

 

[SkipVought]

corycrum,

Please begin a NEW THREAD with your question.

Skip,

_[red][/red]

 

[SkipVought]

"...insert another 2 blank rows below each subtotal line to further separate one group from another. Additionally, I need insert another 2 blank rows below each subtotal line to further separate one group from another. Additionally, I need each subtotal line to be in bold and highlighted in light yellow."

This is really a MS Office question for Forum68.

Inserting empty rows is almost always a poor practice. Consider changing Row Height.

Consider using Format/Conditional Formatting to format "each subtotal line to be in bold and highlighted in light yellow."


Skip,

_[red][/red]

 

[Gavona]

i need to know what the cell refrence is so that i can add a row beneth it so i can do subtotals under it. the find works but i don't know what the statement is for cell refrence.

expression.Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)

expression.Offset(RowOffset, ColumnOffset)



Gavin