PDA

View Full Version : vba holiday sheet



tuggers
08-11-2006, 07:02 AM
Hi,

I am really new to vba so please accept my apologies if this sounds like a stupid question and my code looks very poor, but i am trying to learn.

Below is a code i have made to try and create a holiday tracking sheet for work.

i have a sheet where:-
the date for holiday to be taken, the amount of hours to be taken and username are input, there is also a cell showing the users remaining holiday hours.

this is then to be posted to a second sheet,

this sheet has a date for each day of the year in row 1 from column C onwards
and a list of usernames going down in column "A"
(column b contains a total of remaining hours)

what i need is for the cell on the second sheet to be selected by the row that the correct username is on and the column that the correct date is on.

could anybody please help?

tuggers
08-11-2006, 07:03 AM
Sub holsheet()

Dim HOLHOURS
Dim HOLBOOKING

HOLHOURS = Range("I15")
HOLBOOKING = Range("I18")
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim c As Integer 'Column number
Set Sh1 = Worksheets("PANEL")
Set Sh2 = Worksheets("DATES")

If HOLHOURS >= HOLBOOKING Then

c = WorksheetFunction.Match(Sh1.Range("G18"), Sh2.Rows(1), False)
Sh2.Cells(2, c).Value = Sheets("PANEL").Range("I18").Value
Sheets("PANEL").Select
ActiveSheet.Range("G15").Select
Else
MsgBox ("NOT ENOUGH HOURS!!")

End If

End Sub

This is the code i now have, this works fine if i just want to copy a cell from one sheet to another in a specific cell, but what i need is for the cell that the info is being copied to, to be selected from the date and the username.

mdmackillop
08-14-2006, 12:26 AM
Hi Tuggers
Welcome to VBAX.
Can you sanitise and post a copy of your workbook? Use Manage Attachments in the Go Advanced section.
Regards
MD

Bob Phillips
08-14-2006, 01:08 AM
Where is the name stored?

Asuming it is say G15 then all you need is



Sub holsheet()

Dim HOLHOURS
Dim HOLBOOKING

HOLHOURS = Range("I15")
HOLBOOKING = Range("I18")
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim c As Integer 'Column number
Dim r As Long 'row number
Set Sh1 = Worksheets("PANEL")
Set Sh2 = Worksheets("DATES")

If HOLHOURS >= HOLBOOKING Then

On Error Resume Next
c = Application.Match(Sh1.Range("G18"), Sh2.Rows(1), False)
On Error GoTo 0
If c > 0 Then
On Error Resume Next
r = Application.Match(Sh1.Range("G15"), Sh2.Columns(1), False)
On Error GoTo 0
If r > 0 Then
Sh2.Cells(r, c).Value = Sheets("PANEL").Range("I18").Value
End If
End If
Sheets("PANEL").Select
ActiveSheet.Range("G15").Select
Else
MsgBox ("NOT ENOUGH HOURS!!")

End If

End Sub

tuggers
08-14-2006, 02:36 AM
thanks for the interest guys but i have managed to sort it using the following code.


Sub holsheet()
Dim HOLHOURS
Dim HOLBOOKING
HOLHOURS = Range("I15")
HOLBOOKING = Range("I1")
Dim Sh1 As Worksheet
Dim Sh2 As Worksheet
Dim c As Integer 'Column number
Set Sh1 = Worksheets("PANEL")
Set Sh2 = Worksheets("DATES")
Dim r As Long

If HOLHOURS >= HOLBOOKING Then

r = WorksheetFunction.Match(Sh1.Range("G15"), Sh2.Columns(1), False)
c = WorksheetFunction.Match(Sh1.Range("G18"), Sh2.Rows(1), False)
Sh2.Cells(r, c).Value = Sheets("PANEL").Range("I19").Value
Sheets("PANEL").Select
ActiveSheet.Range("G15").Select
Else
MsgBox ("NOT ENOUGH HOURS!!")
End If

End Sub