Discrepancy formula and VBA Weekday

[Peltz]

Hi there. Im using this formula in Cell N4

="Week "&(N6-($D$4-WEEKDAY($D$4;1)+2))/7+1

N4 has general formatng. This work as a charm. I get the output "Week [and a single digit number]"

However, I need to control this from VBA
The code:

 Private Sub Commandbutton1_Click()

 Range("N4").Value = "Weekday " & (N6 - (C8 - Weekday(C8, 1) + 2)) / 7 + 1
End Sub

.. Returns "Weekday 1,71428571428571"

Somethng is obviously wong here but cant figure out what.

Any input woulde much appreciated.

Tanks

 

[steve the fish]

Even if that was the way you did it they arent the same. The formula references D4 and N6 and your code says C8 and N6. Which is correct?

 

[Peltz]

Sorry, cut the wrond code:

[FONT=Courier New][COLOR=#007700]Private [/COLOR][COLOR=#0000BB]Sub Commandbutton1_Click[/COLOR][/FONT][FONT=Courier New][COLOR=#007700]()

 [/COLOR][COLOR=#0000BB]Range[/COLOR][COLOR=#007700]([/COLOR][COLOR=#DD0000]"N4"[/COLOR][COLOR=#007700]).[/COLOR][COLOR=#0000BB]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#DD0000]"Week " [/COLOR][COLOR=#007700]& ([/COLOR][COLOR=#0000BB]N6 [/COLOR][COLOR=#007700]- (D4[/COLOR][COLOR=#0000BB] [/COLOR][COLOR=#007700]- [/COLOR][COLOR=#0000BB]Weekday[/COLOR][COLOR=#007700](D4[/COLOR][COLOR=#007700], [/COLOR][COLOR=#0000BB]1[/COLOR][COLOR=#007700]) + [/COLOR][COLOR=#0000BB]2[/COLOR][COLOR=#007700])) / [/COLOR][COLOR=#0000BB]7 [/COLOR][COLOR=#007700]+ [/COLOR][/FONT][COLOR=#0000BB][FONT=Courier New]1
End Sub[/FONT][/COLOR]

This is it.

Sorry

 

[steve the fish]

You cant just use 'N6' you need to use Range("N6").Value or better still include the sheet name eg Sheets("Sheet1").Range("N6").Value but as long as the button is on the same sheet as the values this will do:

Range("N4").Value = "Weekday " & (Range("N6").Value - (Range("D4").Value - Weekday(Range("D4").Value, 1) + 2)) / 7 + 1

 

[Peltz]

Ahh, thanks a bunch!