CODEWITHSUNDEEP
excelvbaexcel-formula
William Karamanlis
William Karamanlis

Reputation: 11

If I specify hours and a number of days, then copy the hours into the number of days

I'm trying to do something that seems rather simple, but has some issues in execution. Let's say I have a specific number of hours and a number of days input. I then want to flow those number of hours into each of the number of days on a separate sheet, i.e., 5 days if 5 is input, 3 days if 3 is input, so on and so forth.

  Sheet A      |                               Sheet B
|hours|days|   |    |monday|tuesday|wednesday|thursday|friday|saturday|sunday|
+--+-----+-+   |    ++--++--++--++--++--++--++--++--++--++--+-++--++--++--++-+
|  8  |  5 |   |    |   8  |   8   |    8    |   8    |   8  |    0   |   0  |

Any input would be invaluable!

Upvotes: 0

Views: 38

Answers (2)

Pablo G
Pablo G

Reputation: 199

Use a macro with a shortcut or button with this code

Dim hours, days, Week as Range

Set hours = sheets(“YourSheetName”).range(“CellWithHours”)

Set days = sheets(“YourSheetName”).range(“CellWithDays”)

Set Week = sheets(“YourSheetName”).range(range(“CellWithMonday”), Range(“CellWithSunday”))

For i = 1 to days.value
     Week.Cells(i).value = hours.value
Next i 

For i = 1 to 7
     If Week.Cells(i).value = “” then
            Week.Cells(i).value = 0
     End if
Next i

Upvotes: 0

SJR
SJR

Reputation: 23081

If you have this in SheetA

enter image description here

then this in SheetB. Put this formula in A2 and copy across

=IF(COLUMNS($A2:A2)>SheetA!$B$2,0,SheetA!$A$2)

enter image description here

The COLUMNS formula will increase 1,2, etc as you drag the formula across so once it has gone 6 columns along the formula will return zero.

Upvotes: 3

Related Questions