Posts by joseph_r03

Re: Update Macro, Row Changes

If row 80 is your last row of data, then this should work

Sub GrandTotal ()
Dim rngHide As Range


Set rngHide = Range("a65536").End(xlUp)
rngHide.EntireRow.Hidden = True


End Sub

HTH

Re: Update Macro, Row Changes

What's your macro?

Re: Range Selection Vba

Works for me too.
Thank you very much.

Re: Range Selection Vba

Thanks for replying Norie,

One comment. ARR was used rather than AAR on a few occasions.

That actually did my formatting for one row + the number of rows my data has in it (all below the data), rather than just the one row below my data.

I attached a small example.

Hi all,

I do not know VBA vocab so please pardon my title and description if I am using incorrect verbage.

I am trying to remove all areas in my code with .select and selection.
This first sample works properly. This piece is meant to take the cells one row below my data and highlight it yellow and include top and bottom borders.

sub test()


dim rngAAR as range, rngAARStepDown as range, rngAARTotal as range


[B]Range("A1").Select[/B]
       
    'Format subtotal line for AAR
    [B]Set rngAAR = Selection.End(xlToRight).End(xlDown)[/B]
    Set rngAARStepDown = rngAAR.Offset(1, 0)
    Set rngAARTotal = Range(rngAARStepDown, Cells(rngAARStepDown.row, 1))
        rngAARTotal.Font.Bold = True
        rngAARTotal.Interior.ColorIndex = 6
    With rngAARTotal.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        End With
    With rngAARTotal.Borders(xlEdgeBottom)
        .LineStyle = xlContinous
        .Weight = xlThin
        End With


end sub

I have tried to change it to the below and my worksheet has no changes made to it, and there is no error message. I "step into" the whole way through and there is no formatting change made.

sub test()


dim rngAAR as range, rngAARStepDown as range, rngAARTotal as range
    
    'Format subtotal line for AAR
    [B]Set rngAAR = A1.End(xlToRight).End(xlDown)[/B]
    Set rngAARStepDown = rngAAR.Offset(1, 0)
    Set rngAARTotal = Range(rngAARStepDown, Cells(rngAARStepDown.row, 1))
        rngAARTotal.Font.Bold = True
        rngAARTotal.Interior.ColorIndex = 6
    With rngAARTotal.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlThin
        End With
    With rngAARTotal.Borders(xlEdgeBottom)
        .LineStyle = xlContinous
        .Weight = xlThin
        End With


end sub

I have also tried using

Set rngAAR = [B]"A1"[/B].End(xlToRight).End(xlDown)

but get the message "Expected End of Statement" at the period mark after "A1"

Any suggestions?
Thanks for your time

Re: Sumif On Date Range

Try
=SUMPRODUCT((B11:B26<$A$6)*(C11:C26))

HTH

Re: Search Numbers Within Ranges

Try this one

Re: Search Numbers Within Ranges

See the attached.
I added a helper column, G

HTH

Re: Creating A Cell Reference Within A Range Reference

I tested this on a much simpler formula, but try:

=SUM(IF(INDIRECT("SHIPPED ORDERS!"&"$K$2:"&"$K$"&INDEX(SHIPPED ORDERS!$A$1:$B$1,1,1))=$B6,IF((INDIRECT("SHIPPED ORDERS!"&"$Q$2:"&"$Q$"&INDEX(SHIPPED ORDERS!$A$1:$B$1,1,1))<=(D$2))*(INDIRECT("SHIPPED ORDERS!"&"$Q$2:"&"$Q$"&INDEX(SHIPPED ORDERS!$A$1:$B$1,1,1))>=(D$1)),INDIRECT("SHIPPED ORDERS!"&"$S$2:"&"$S$"&INDEX(SHIPPED ORDERS!$A$1:$B$1,1,1)),0),0))

HTH

Re: If Statement With Vlookup For Multiple Columns

Your welcome
Glad it worked

Re: Lookup Within A Cell's Data

What do you mean by "do a reconciliation between the two"?
If the email string has a number in it that matches the cleanly exported numbers you want them identified?

Re: If Statement With Vlookup For Multiple Columns

Assuming your unique number is in column A and your data is in B:E, in F4 use =IF(COUNTIF(B4:E4,A4),A4,0)

HTH

Re: Formula Error

could you provide an attachment?

Re: Return Value Based On Two Conditions

See if the attached helps

Re: Formula Which Counts Items For Variable Number Of Attributes

Sheet 1 has the data
Sheet 2 has the attribution list used for data validation in A4:A7
Sheet 2 has the data validation in B4

Formula in C4 is:
=COUNTA(INDIRECT("Sheet1!"&LOOKUP(MATCH($B$4,Sheet1!$A$4:$D$4,0),{2,3,4},{"B","C","D"})&5):INDIRECT("Sheet1!"&LOOKUP(MATCH($B$4,Sheet1!$A$4:$D$4,0),{2,3,4},{"B","C","D"})&100))
This will tell you how many items have an "X" under the selected attribute

HTH
joseph

edit: This assumes your table headers in Sheet1 are in the range B4:D4

Re: View Details By Person

Thanks AAE. Great suggestions.

Re: View Details By Person

Thanks all for your replies.

jlsprink, I do like your solution and I think I can replicate it for all of my columns. The reason I don't think a pivot table would be good (may be lack of user knowledge) is that I would have to insert the data on a separate sheet in the same format as what I want my final product to look like. I think it would be best for reviewing expenses months down the road, but not for initial inputting of data.... hmm I may tinker with both options and see which one I prefer.

Hello all

I am creating a personal expense tracking sheet for the upcoming year. I have each day of the year going down as my rows. My column headers are various expenses I expect to have, such as a phone bill and internet bill. I want a way to pick which expenses (columns) are visible. I expect this to aid in the inputting of expenses and the reviewing of expense types. I know of course, I could just click all the columns I don't want and hide them, but I'm looking for a quicker way if possible.

So, if I want to record the phone bill, or just look at the phone bill expenses alone, I could use a drop down which listed all my column headers and check which one(s) I wanted to view. I thought of like the way a pivot table option looks to pick what you want to view /shrug.

Maybe a drop down is not the way to accomplish this, and maybe my spreadsheet should be laid out differently. I'm open to suggestions.

Thanks,
Joseph

Re: Calculate X When Quantities Changes

See if Goal Seek works for you

Re: Visual Basic Resource

It's no forum, but this teach yourself guide might be helpful
http://www.informit.com/librar…px?b=STY_VB6_24hours&rl=1