# Posts by Luke M

• ## Timesheet for double time if over 48 on the 7th day

Re: Timesheet for double time if over 48 on the 7th day

Hi DrCustUmz,

See if this will work for you. It's not dynamic to hadnle if you start having a different pair of days as your 2 days off, but the calculations seem to work.
I changed the last row to calculate the equivalent paid hours. So, takes your regular hours + 1.5 * overtime hours. The Double row now just simply says "Yes" if we need to double the hours for that day. Once we have all the equivalent hours, we can add them up and multiply by the rate.

• ## How do I access workbook and worksheet selected by UserForm in a separate module?

Re: How do I access workbook and worksheet selected by UserForm in a separate module?

When the user has made their selections in UserForm, or are you using Hide or Unload? Since you need the values later in your code, you'll want to make sure you're doing a Hide.

So, whatever button you have to close your form (after user makes selection) will have something like

Code
Me.Hide
• ## Copy data from multiple workbooks into one workbook sharing the same header

Re: Copy data from multiple workbooks into one workbook sharing the same header

Hi asmod, and welcome to the forum!

Check out the code examples here, from Ron: http://www.rondebruin.nl/win/section3.htm

you should be able to use his free Add-in to merge everything, or use one of the other workbook merge examples.

• ## Returning a Formatted Number from a IF statement.

Re: Returning a Formatted Number from a IF statement.

Hi Keljor, and welcome to the forum!

Your formula is the culprit, in that it is returning a number stored as a text string, not a true number, so none of your cell formatting will take affect. You would want your formula to be this:

=IF(NUMBER<=0, 0, WhatGoesHere)

or perhaps, simply:

=MAX(NUMBER, 0)

if the goal is to fetch the any number greater than 0.

Now you can apply a format to the cell with the formula, and it'll actually do something.

• ## VBA script: To copy missing rows from one worksheet and insert in another sheet

Re: VBA script: To copy missing rows from one worksheet and insert in another sheet

Hi mailtodp, and welcome to the forum!

I believe this code will work for you. Just might need to change the sheet names at beginning.

• ## VBA - Refer to a Column in Cell

Re: VBA - Refer to a Column in Cell

Hi toofxd, and welcome to the forum!
You were close. Need to specify that you wanted the values from those ranges. Could do this in one step, but spacing it out for visibility

• ## HLOOKUP of text with multiple criteria

Re: HLOOKUP of text with multiple criteria

Try something like this:

=INDEX(B2:E5, MATCH(ChosenName, A2:A5, 0), MATCH(ChosenCountry, B1:E1, 0))

• ## compare array value in a cell with another cell and replace

Re: compare array value in a cell with another cell and replace

I would do a text to columns on the data to split it first.Then you can cut/paste to get it all back into a single column. Then, a formula like

=IFERROR(VLOOKUP(A2,'[Other Book.xlsx]Sheet1!A:B, 2, 0), "")

will fetch the results from one workbook to the other.

• ## Cannot figure out how to add all these together

Re: Cannot figure out how to add all these together

try this:

• ## Error 1004: Application.Dialogs(xlDialogOpen).Show Mac Excel 2016 Importing Data

Re: Error 1004: Application.Dialogs(xlDialogOpen).Show Mac Excel 2016 Importing Data

Thanks for link to Ron's site. Using his page, it looks like this should work. I've tried to clearly comment where I've pieces your bit of code in.

• ## Error 1004: Application.Dialogs(xlDialogOpen).Show Mac Excel 2016 Importing Data

Re: Error 1004: Application.Dialogs(xlDialogOpen).Show Mac Excel 2016 Importing Data

Will this work? (I don't have a Mac, just changed syntax to what would work on PC)

• ## Find text in column then (in that row) add today's date in another column

Re: Find text in column then (in that row) add today's date in another column

You are correct, we need to add an additional check to make sure col M is blank.

• ## Find text in column then (in that row) add today's date in another column

Re: Find text in column then (in that row) add today's date in another column

Try giving this a shot.

• ## Copy Rows From One Worksheet To Another If Condition Meets Criteria

Re: Copy Rows From One Worksheet To Another If Condition Meets Criteria

Check out Ron's code on "Copy records with the same value in a column to a sheet or workbook", I think it'll do exactly what you're describing.

http://www.rondebruin.nl/win/s3/win006.htm

• ## Automatically update one master sheet from multiple sheets

Re: Automatically update one master sheet from multiple sheets

You should be able to use the Data - Remove Duplicates feature? Record a macro of yourself using it to get the basic code you need. Something perhaps like

Code
MasterWs.Range("\$A\$1:\$D\$1000").RemoveDuplicates Columns:=Array(1, 2, 3, 4), Header:=xlYes
• ## Out of memory error

Re: Out of memory error

this line looks suspicious:

ActiveCell.EntireRow.Value = dateValue

It's trying to apply a value to an entire row...maybe it's just supposed to be a single cell?

Here's your whole code, simplified down a bit. Removed selections, turned off screen updating. Cleared clipboard at end.

• ## Increment cell reference by 18 and fill down formulae X 1000 times via Macro

Re: Increment cell reference by 18 and fill down formulae X 1000 times via Macro

With the attached file (I updated your formulas), you can use this macro.

• ## Increment cell reference by 18 and fill down formulae X 1000 times via Macro

Re: Increment cell reference by 18 and fill down formulae X 1000 times via Macro

Here's a start. Your file didn't include any formulas, or indicate what they should be, so I just left that spot open in the code.

Or, if you've already got a somewhat working code, you might be able to use the offset tool. Something like

Code
Dim myCount as Long

myCount = 0
Do until myCount = 100 'Some loop condition
Range("A1").Offset(myCount * 18).Value = Range("B1").Offset(myCount * 18).Value
myCount = myCount + 1
While
• ## VBA Help needed to tweak my Search and Find macro...

Re: VBA Help needed to tweak my Search and Find macro...

Since you already have an Exit Sub after the Search, I think you can just do this

• ## Nested AND and OR statements with different THEN outputs

Re: Nested AND and OR statements with different THEN outputs

Simpler solution, if you're interested.

=G5+5*(D5<A10)+5*(E5<12)