# Posts by HowardC

• ## search functionality on User Form

I have a user form with 20 items on it to select

I would like to know how to set up a search button on the user form to search for an item for e.g. if one of the items is "Open Journal folder" , I would like to be able to search for e.g. Journal and it will take me to this item on the user form

Your assistance regarding the above is most appreciated

• ## Macro to hide Pivot Table Rows where Col E has zero values

I have attempted to write code to hide rows on a Pivot table where Col E has zero values from row 4 onwards

Code
``````Sub Hide_Zeroes()
Dim LR As Long
LR = Cells(Rows.Count, "A").End(xlUp).Row
For I = LR To 4 Step -1
If Cells(I, 5 & LR).Value = 0 Then Cells(I, 5).EntireRow.Hidden = True
Next

End Sub``````

I need someone to kindly assist me

• ## Increment Number each time a copy is printed

Re: Increment Number each time a copy is printed

Thanks for the help,much appreciated

• ## Increment Number each time a copy is printed

I have the following code which I have tried to wrote that will automatically increment the Quote # each time a quote is printed

I have inserted this into my workbook

when Printing the Quote # is not being incremented by 1

for eg 1000001 to change to 1000002 after printing ,the third print 1000003 etc

It would be appreciated if someone could assist me

• ## Compute Average Days and total value by division

Re: Compute Average Days and total value by division

Thanks for the Help Alan

• ## Compute Average Days and total value by division

I have raw data in sheet "Data" and on my Summary sheet I would like to compute the following for each division (Branch)

1) Average Days in Inventory based on the Division selected on sheet "Summary
2) Total Value based on Division selected

I have tried to set up formulas on my sheet "Summary , but cannot get these to work

I have shown what the values should be on B6 & C6

It would be appreciated if someone could kindly assist me

• ## Macro to print data on two pages only

Re: Macro to print data on two pages only

I managed to resolve the problem. I went into Page review and amended the range. I then ran the macro and it worked perfectly

• ## Macro to print data on two pages only

I have VBA code below which I need to print from A2:Q112 on sheet "Comm Br1"

However, when running the macro, for some unknown reason, the range includes col R, resulting in 4 pages being printed as the range is too large to print on two pages

I do not want Col R included in the print, but cannot work out why it prints this range

It would be appreciated if someone could assist me in resolving this

Code
``Sub Print_CommBR1 ()   Sheets("Comm_Br1").Select LastRow = Sheets("Comm BR1").Cells(Rows.Count, "A").End(xlUp).Row With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .PrintArea = "\$A\$2:\$Q\$112" .PrintGridlines = True .PrintTitleRows = "" .PrintTitleColumns = "" .LeftHeader = "&D&T" .CenterHeader = "Comm BR1" .Orientation = xlLandscape End With End With Application.ScreenUpdating = True ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True      Sheets(2).Select End Sub``

I also posted two days ago on Mrexcel.com

• ## Copy certain criteria

Re: Copy certain criteria

Thanks for the help, much appreciated

• ## Copy certain criteria

Re: Copy certain criteria

Robert, If I filter the Creditors tab accordingly how do I go about exporting only the visible rows to the Query Items tab ?

• ## Copy certain criteria

Re: Copy certain criteria

Hi Robert

Thanks for he help. Your code works perfectly

The reason I wanted code and not to use filters, is that once the data is copied to "Query items" tab, I export and email this tab only

• ## Copy certain criteria

I have tried to write code to copy data from sheet creditors where "QRY" appears in Col Q and the value in Col V <> 0 to sheet "Query items" to Col AQ8 onwards

Where the above criteria exists then the data must be copied from A5 to the last row in Col V , where "QRY" appears in Col Q and Col V <> 0

when running my code, no data is pasted in Col AQ8 on sheet "Query Items"

–see my code ‘Sub Copy_Queries_QRY()”

I have manually copied the data to show you what it should look like after running the macro.

It would be appreciated if someone could kindly assist me

I have also posted on http://windowssecrets.com/foru…based-on-certain-Criteria 4 days ago

• ## Extract Account numbers based on certain criteria

Re: Extract Account numbers based on certain criteria

Thanks very much for the help

• ## Extract Account numbers based on certain criteria

I have account numbers in Col A and account numbers in Col D from row 2 onwards. In Col G from row 2 onwards I have specified "claimed" or "Not Claimed"

"
I need a macro or a formula to compare the account numbers in Col D with those in Col A. Where "claimed" appears in Col G, I need to extract the account numbers in Col D, that does not appear in Col A.

I need these extracted to Col P

I have also posted on Mrexcel.com

http://www.mrexcel.com/forum/e…sed-certain-criteria.html

I have attached a sample workbook

• ## Conditional Format Macro

Re: Conditional Format Macro

Thanks for this as well

• ## Conditional Format Macro

Re: Conditional Format Macro

Thanks very much for the code

I changed the colorindex from = 10 to 6 (yellow)

• ## Conditional Format Macro

I have a sheet called "Variance" that is cleared and a macro updates this sheet

I have written code so that if "Bad debts Provision" appears on this sheet on Col E, then it must be highlighted in Yellow using Conditional formatting

When running the macro, I get subscript out of range and the following code is highlighted

Code
``Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority``

See full code below

It would be appreciated if someone could kindly amend my code

I also posted this on MrtExcel.com

http://www.mrexcel.com/forum/e…onal-format-variance.html

• ## Macro to clear text

Re: Macro to clear text

Thanks for the help

• ## Macro to clear text

Re: Macro to clear text

Thanks for reply. It is text colored using fill color. There is no conditional formatting. I am using Office 2013

• ## Macro to clear text

I need a macro to clear all text and values , except colored data