# Posts by legin52

Roll

• ## Calculate No of Working days between two specific days excluding weekends

OK first step is to create a table of weekend dates (and or Holidays).
somewhere possibly on a new sheet which can be hidden later if required, in cell A1 (for example) Type in the date 01/01/2018 in B1 type in the day on which your chosen date fell in 2018 it was a Monday then by highlighting the 2 cells and grabbing the autofill cross and dragging down to a date you want, say 31/12/2018. This will give you a calendar. now apply a filter for Monday through Friday to be visible then delete them. This will leave you a list of all the dates for all the Saturdays and Sundays through the period.
At this point I would make use of the name manager to use in the later formula.
This has the added benefit of you being able to add specific bank holidays such as Easter and Christmas (by doing it manually if required.
Now I am going to send the sheet I created so you can see the formula and my construction might be of help.

Hope it helps
Regards
legin52 [ATTACH]n1204944[/ATTACH]

• ## Calculate No of Working days between two specific days excluding weekends

Ooops! my mistake. :0ops:
Quite new to Forum activity anywhere.
Will compose my possible solution and post shortly.
Regards
legin52

• ## Calculate No of Working days between two specific days excluding weekends

I know this was 2 years old but if you haven't got a solution and you would still like one then I have one for you. Let me know.
Regards

• ## A math calc from different sources with neat blank cells

Hello everyone.
I need help to reduce the chances of me having to do a comb over to hide the increasing self created bald patch.

I have a spread sheet (attached example of) that takes data from different sources and actions it in a cell

I need it to look neat for presentation purposes so blank cells where possible.

In the attached SS I have typed the steps required in order so all the info I have is contained within, I hope,

I trust one of you good people will be willing and able to help

Thanks Legin 52 [ATTACH]n1204894[/ATTACH]

• ## Variable number of rows of data

Hello.
I am hoping somebody can help me as my knowledge and logic have been exhausted.

Problem:
I have a workbook with 2 sheets in it.
‘Printer Macro’ from here known as ‘1’
‘Printer Header Sheet’ from here known as ‘2’

A set of data is imported to ‘1’. It occupies columns A to L
However the number of rows occupied changes each time the project is run and it is this that is causing me problems.
What I am trying to achieve is:
Step1: Select A:L from row 1. Copy the data. Go to ‘2’ and paste (values and format) into cell A2 of this sheet. Then print sheet ‘2’.
Step 2: Repeat Step 1 but with Row 2 (A:L from ‘1’). Then paste into the same cell as step 1 (i.e. A2 of sheet ‘2’). Then print sheet ‘2’
This repeats for every row in sheet ‘1’ with data in it.
How can I get it to repeat the actions for all lines with data, one at a time, but stop if there is no data on a row (this being the end of the Macro?.
Suggestions will be gratefully appreciated

• ## Adding time to create cell with 2 hyphenated times in it

Re: Adding time to create cell with 2 hyphenated times in it

Oh! my ***!

Hours, trying everything I knew or thought might work.

Obviously I don't know enough.

KjBox does.

My Grandchildren will thank you in the future as I do now.

• ## Adding time to create cell with 2 hyphenated times in it

Re: Adding time to create cell with 2 hyphenated times in it

TGry that again..

[ATTACH=CONFIG]73670[/ATTACH]

• ## Adding time to create cell with 2 hyphenated times in it

Hello people of talent and knowledge. I hope one of you can help.

I have a sheet, into column N of which, I will be pasting a ‘time’. As pasting continues down the column, the time will vary, increasing or decreasing but only by the full hour. It will be formatted as “09:00” for example.

I would like each cell in column M to take the time in the related cell in column N (i.e. M2 to ‘see’ N2) return it, add 1 hour to it and return the time formatted as “09:00 – 10:00”.

• ## Formatting of dates in user forms

Re: Formatting of dates in user forms

Yeesssss!

Double thanks

Worked a treat.

eternally grateful

Regards

• ## Formatting of dates in user forms

Re: Formatting of dates in user forms

Thanks for the speedy response.
I did as you suggested (unless Mr stupid here has misinterpreted your instruction)

1. Deleted the following:

Code
``````Private Sub UserForm_Initialize()
txtDate.Text = Format(Now(), "dd/mm/yyyy")
End Sub``````

2. The changed:

Code
``ws.Cells(newRow, 2).Value = Me.txtDate.Value``

With

Code
``ws.Cells(newRow, 2).Value = Date``

I can enter the date in the user form as I want but on the click to enter the data onto the spreadsheet today's date is entered
[ATTACH=CONFIG]66440[/ATTACH]
It can only be me, please tell me where I've gone wrong.
Regards

• ## Conditional Formatting Help

Re: Conditional Formatting Help

Scratch bit about leaving it at work
Just found a copy that I was working on prior to the finished article

See attached

Investigate the clock and the macro to create it
look at the conditional formatting (not forgetting you can copy and paste formatting (including 'conditional')

Hope that helps
Regards

• ## Conditional Formatting Help

Re: Conditional Formatting Help

Hi,
I did this myself a few weeks ago but my problem was to change the cell colour based upon a time rather than a date. The solution I found was to install a clock on the spreadsheet. It can be formatted to include the date as well as the time. Then you set your deadline and when it goes past that the relevant formatting kicks in.
I suggest you put the clock on a separate sheet then hide it but ensuring the clock applies to the workbook.

I could send you a copy of mine but it is at work and I am now on a weeks holiday, but if you want....

• ## Formatting of dates in user forms

Hello
So, I have created a user form to enter data onto a spreadsheet. The problem is....(Please see code below)when the form opens it automatically puts today's date in the "txtDate" box and when the enter key is pressed it loads the date in the desired format onto the spreadsheet. The desired format is dd/mm/yyyy.

If I manually change the date in the user form it reverts back and enters on the sheet as mm/dd/yyyy

However I don't want to use the "Today()" what I have been trying (many, many ways) is to leave the date box clear on the user form and manually enter the date (dd/mm/yyyy) in the user form and to, when the enter button is pressed to have it transferred to the spreadsheet in the same format.

It is the last three lines that is giving me a hair pulling problem (I am now bald).

To learn should be desired, to help is inspiring.
Please 'inspire' me and quench a little of my 'desire'.
Legin52

• ## Macro must read changing cell content

Re: Macro must read changing cell content

OK Thanks for the pointer and here is the workbook in question[ATTACH=CONFIG]60630[/ATTACH]

• ## Macro must read changing cell content

Firstly I am something of a novice at Macros – I have to use the recorder to create them as writing VBA is a bit of a mystery. Please bear this in mind if you choose to reply to my query.
My macro operates a series of steps which start by applying a filter to a data input sheet.
It takes a number from a cell on a different sheet and uses that in the filter.
My problem is that the ‘recorder’ has recorded the cell (E2) as the one to copy. It currently (at the time of recording the macro) contained “003”. The following week’s data means that ‘E2’ may contain a different number eg “010”. But when the macro runs it still ‘sees’ “003”.
It then goes onto read cell “F2” on the same sheet and has the same problem with the contents “003Wk17”
I need the macro to recognise the contents of the cell at the time the macro runs rather than staying with what was in the cell at the time of the recording the Macro.
I have included my coding to help
Any help will be gratefully received.

• ## Copy cell to accommodate cell variable content in VBA

Re: Copy cell to accommodate cell variable content in VBA

Ger, sorry for not posting a thanks but I am the primary carer for my mother who has MND and it means that sometimes I don't get a lot of time.

Anyway thanks for this it works and does 'see' what ever value is in cell F2 and then runs the report, just fine.

However there is a secondary issue.

Once the above macro has selected the F2 data and run the report, it then sends the reported data to a new workbook. Here the sheet tab is renamed by using the contents of G2 on the same sheet as the F2 cell. Unfortunately your suggestion doesn't use the value in G2 it just uses the "006WK20" See line 15 in my original code (4 line section starting "Range("G2").Select". I tried adding to your code by changing "range("f2")" to "range("f2:g2") - but this did not work. I feel I am within touching distance but I am staggering to the finish line. Can you help?

With kind regards and hope
Legin

• ## Copy cell to accommodate cell variable content in VBA

Re: Copy cell to accommodate cell variable content in VBA

Hi sorry I'm a bit new to this. You are right the critical part of my mail is "I need the Macro.....Blah! blah!"

here is the first part of the code

First line selects a cell, whose data can change. Line 4 above shows the data in the cell which in this case is "006".

I need it to just copy the cell F2 whatever the data in the cell is.

Hope this explains. Maybe I should say I am a novice at writing the macro. I create macros by recording the task as I perform it. I can read and see the language in the macro but am not confident at writing it.