Sick record is making me ill! Please help.

  • Thread starter Thread starter CrankyLemming
  • Start date Start date
C

CrankyLemming

I have a problem with a sick record sheet & got loads of help last
time I had a query. So I'm on the scrounge again now that the rules
have been complicated. I hope this makes sense:

Say columns R and S are for an ever-growing list (well, R/S1 to R/S20
), the cells B1,D1 and F1 contain dates; C1, E1 and G1 contain a
number (number of days sick this time). I have a macro ("MOVE_RIGHT")
that cuts F1 and shifts the contents of B1 and D1 to the right (B1
moves to D1, D1 moves to F1, B1 is thus empty for input; same
principle with the cells containing numbers).

What I want to be able to do do is get the 'cut' contents from cells
F1 and G1 to appear in the _next_ available cell in columns R and S.
For example, R1, R2 and R3 are already filled from earlier sickness. I
run "MOVE_RIGHT", and the contents of F1 paste to R4, do it again and
they write to R5.

Thanks for any help.

Steve
 
Are you really filling up Column R (not moving to the right, but moving
downward)????

If yes, then this might fit into your code somewhere???

Dim DestCell as Range
with Activesheet
set destcell = .cells(.rows.count,"R").end(xlup).offset(1,0)
end with

'some cutting takes place.
With ActiveSheet
.Range("a1").Cut _
Destination:=DestCell
End With

You can't cut|paste special|transpose.
but you can cut|paste, cut|paste, etc.

If this doesn't help, post back with a bit more code. I'm sure someone will
jump in.
 
Dave said:
Dim DestCell as Range
with Activesheet
set destcell = .cells(.rows.count,"R").end(xlup).offset(1,0)
end with

'some cutting takes place.
With ActiveSheet
.Range("a1").Cut _
Destination:=DestCell
End With

You can't cut|paste special|transpose.
but you can cut|paste, cut|paste, etc.
Click to expand...

I managed to get it done by just recording and cut'n'paste-ing, but
this looks a lot tidier; thanks for that. I'll give it a go.

Now, sorry to be a nuisance, but if I have two cells a1, b1 that
contain dates, how do I get Excel to give me a total number of
weekdays (IE not counting Saturdays and Sundays) between those dates?

TIA again

Steve
 
Just to add to Frank's reply. If you want to use Networkdays in a worksheet,
you'll need to load the analysis toolpak. (Tools|addins). If it wasn't copied
from the distribution disk (CD?), you'll be prompted for the disk.

And if you want to use that function within your code, you could do any of
these:

Option Explicit
Sub testme()
With ActiveSheet
MsgBox networkdays(.Range("a1").Value, .Range("b1").Value)
MsgBox Application.Run("atpvbaen.xla!networkdays", _
.Range("a1").Value, .Range("b1").Value)
MsgBox .Evaluate("networkdays(a1,b1)")
End With
End Sub

The first two versions require that the analysis toolpak-VBA is loaded in excel
(tools|addins).

The first msgbox line requires a reference to the analysis toolpak for VBA
(tools|references|and check atpvbaen.xls). With that set, you can this function
just like it was built in.

The second needs to have the atpvbaen.xls addin loaded, but doesn't require the
reference in VBA.

The third doesn't need the atpvbaen.xls addin even loaded, but does require the
analysis toolpak itself.
 
Back
Top