Posts by Barrie Davidson

Re: Print Hidden Dynamic Worksheet

Quote from beeker

Thanks Barrie,
I am using the method you suggested. However, it gives me one additional blank page at the very end. I have checked everywhere for some type of contents in the referenced cells but nothing but a formula present. Some worksheets are 2 pages of information and it prints 3. Is there something I have missed here. Unfortunately, I embedded this on 54 worksheets b4 I did a complete workbook printing, yuck

Thanks
Beeker

Do you mean that there's formula in column A that's returning a blank (and is being printed)? If yes, what column could be used to determine the last row?

Re: Print Hidden Dynamic Worksheet

Would this work?

Application.ScreenUpdating = False
Sheets("Pricing").Visible = True
Sheets("Pricing").PageSetup.PrintArea = "$A$1:$L$" & _
    Sheets("Costing").Range("A65536").End(xlUp).Row
Sheets("Pricing").PrintOut
Sheets("Pricing").Visible = False
Application.ScreenUpdating = True

Re: Sorting with refs to other sheets

If you use absolute cell reference (i.e., =Sheet1!$A$1 instead of =Sheet1!A1), you'll be able to sort and not lose the link.

Re: Sorting with refs to other sheets

Is there a reason you can't combine the worksheets and just have one?

Re: ignoring messageboxes when deleting tabs?

Before your code that deletes: Application.DisplayAlerts = False

After your code that deletes: Application.DisplayAlerts = True

Regards,

Re: nested If between x and y then.. in vba

What about

"=IF(AND(R[-1]C[-7] - R[-1]C[-8]>=60,R[-1]C[-7]" & _
    "- R[-1]C[-8]<=75),0.3,IF(AND(R[-1]C[-7] - R[-1]" & _
    "C[-8]>=150,R[-1]C[-7] - R[-1]C[-8]<=180),0.66,I" & _
    "F(AND(R[-1]C[-7] - R[-1]C[-8]>=250,R[-1]C[-7] - " & _
    "R[-1]C[-8]<=365),1,0.5)))"

Does that work for you?

Re: reading values between 2 spread sheets

In spreadsheet1 I would use a VLOOKUP in a blank column and then copy the values over to column C. Assuming you want to maintain the costs in column C if there's no match in spreadsheet2, use something like this in spreadsheet1:

=IF(ISNA(VLOOKUP(A1,[spreadsheet2.xls]sheet1!$A$1:$C$300,3,0)),C1,VLOOKUP(A1,[spreadsheet2.xls]sheet1!$A$1:$C$300,3,0))

Does this help?

Re: Compare ranges in 2 sheets and delete/add cell depend on a value in different cell

In a blank column in the monthly report, use this formula (change the range on project list accordingly):
=MATCH(A1,'Project List'$A$1:$A:$100,0)
Copy this all the way down. Then filter on the errors and delete the rows.

Hope this helps.

Re: Sort Datas by Last 2 weeks dates

Try this and let me know if it works for you.

Dim Class As String
Dim FilterRange As Range


Class = ComboBox1.Value
Set FilterRange = ActiveSheet.Range("A1:AK" & _
    ActiveSheet.Range("A65536").End(xlUp).Row)
FilterRange.AutoFilter field:=1, Criteria1:=Class
FilterRange.Sort Key1:=Range("I2"), Order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


FilterRange.AutoFilter field:=1
FilterRange.AutoFilter field:=9, Criteria1:=">=" & Format(Date - 14, "mm/dd/yyyy"), _
        Operator:=xlAnd

Regards,

Re: Sort Datas by Last 2 weeks dates

Please confirm - are you only sorting column I and you don't care about the data in the other cells?

Re: Sort Datas by Last 2 weeks dates

Can you post your code?

Re: Duplicate Alerting

=IF(COUNTIF(C5:C12,5)>1,"Duplicate Fives","OK")

Regards,

Re: Autofilter Using Form Value

Try this (not tested):

Selection.AutoFilter Field:=13, Criteria1:="=*" & TxtDescr.Value & "*", Operator:=xlAnd"

Hope this helps out!

Re: Worksheet Tabs

You could try something like:

Sheets("Sheet3").Range("A1").Value = ActiveSheet.Name

This codes assumes you want to put the sheet name in A1 on sheet "Sheet3" and that the new sheet is the active sheet.

Regards,

Re: VBA for ISNUMBER function

If IsNumeric(Range("B13").Value) Then
    Range("B13").Copy
End If

Second question, yes it will work.

Does your range contain a header row?

How about using:

=MONTH(DATEVALUE(A1))

(assuming the string is in A1).

Regards,

I'm assuming the value referencing the time that you want to convert is a string (text). With that in mind,

=VALUE(LEFT(A1,LEN(A1)-6))+VALUE(MID(A1,LEN(A1)-4,2))/60+VALUE(RIGHT(A1,2))/6000

EDIT: Welcome to the board.

Regards,

Is there a hot key to access the menu item? Something like ALT+M, etc.

Regards,

Try this formula:

=IF(ISNUMBER(SEARCH("PB",B2)),"PB", "Other")

PS - Welcome to the board!

Regards,