Results 1 to 10 of 10

Thread: A quick dim cell thing
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2002
    Location
    Australia
    Posts
    583
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default A quick dim cell thing

    OK, here is some of my code.

    Basically i am choosing that z1 will give me the value in the cell that is the same col as active cell, but in row 4. So if active cell was D10, then z1 would equal the value that is in cell D4

    Thank you

    Dim x
    x = ActiveCell.Column
    Dim y
    y = ActiveCell.Row
    Dim z1
    z1 = x4.Value

  2. #2
    Board Regular
    Join Date
    Aug 2002
    Location
    Australia
    Posts
    583
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    OK, here is my entire code, I have had a crack at it but am not that good with dimming stuff yet, thanks. NB: I realise this adds the number of months equivelant to the actual cell x4 in case 1 not what i want it to (x being the dim thing)

    Sub UpdateActiveCell()

    Dim x
    x = ActiveCell.Column
    Dim y
    y = ActiveCell.Row
    Dim z1
    z1 = Range("x4").Value
    Dim z2
    z2 = Range("x18").Value

    If x > 11 Or x < 2 Or y > 29 Or y < 6 Or y = 16 Or y = 17 Or y = 18 Or y = 19 Then
    MsgBox ("The slected cell can not be updated this way")

    ElseIf y > 5 And y < 16 Then
    ActiveCell.Value = DateAdd("m", z1, Range("E31"))

    ElseIf y > 19 And y < 30 Then
    ActiveCell.Value = DateAdd("m", z2, Range("E31"))

    End If
    End Sub

  3. #3
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,753
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default

    Is this what you're trying to do?

    Code:
    Sub test()
    Dim z1, z2, x As Integer, y As Long
    x = ActiveCell.Column
    y = ActiveCell.Row
    z1 = Cells(4, x)
    z2 = Cells(18, x)
    If x < 11 And x > 2 Then
        Select Case y
            Case 6 To 15
                If IsNumeric(z1) Then ActiveCell.Value = DateAdd("m", z1, Range("E31"))
                Exit Sub
            Case 20 To 29
                If IsNumeric(z2) Then ActiveCell.Value = DateAdd("m", z2, Range("E31"))
                Exit Sub
        End Select
    End If
    MsgBox ("The selected cell can not be updated this way")
    End Sub
    Office 2010/365

  4. #4
    Board Regular
    Join Date
    Aug 2002
    Location
    Australia
    Posts
    583
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Thanks Hot Pepper, it works a treat.......only 1 thing, if the value in row 4 or 18 is a decimal (the number refers to months - and if I have .5 which means half a month or 2 weeks) then I get the same date as the value in E31, it doesn't increment half a month (or 2 weeks).

    Any suggestions?

    NB: at this stage there is only 1 occurrence of a decimal (.5) and all the others are full numbers

  5. #5
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,753
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default

    Is .5 the only possibility for the decimal values?
    Office 2010/365

  6. #6
    Board Regular
    Join Date
    Aug 2002
    Location
    Australia
    Posts
    583
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    it will always be a .5

  7. #7
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,329
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Dude,

    Have a look at the Offset function.

    For example, ActiveCell.Offset(0,4)
    will refer to the cell that is offset from the ActiveCell by 0 rows and 4 columns, which appears to be what you are trying to achieve.

    HTH
    Richie

  8. #8
    Board Regular
    Join Date
    Aug 2002
    Location
    Australia
    Posts
    583
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Richie, Hot Peppers help worked for me Now my question relates to the .5 of a month

    Thanks

  9. #9
    MrExcel MVP
    Moderator
    Scott Huish's Avatar
    Join Date
    Mar 2004
    Location
    Oregon
    Posts
    19,753
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    4 Thread(s)

    Default

    How about:

    Code:
    Sub test()
    Dim z1, z2, x As Integer, y As Long
    x = ActiveCell.Column
    y = ActiveCell.Row
    z1 = Cells(4, x)
    z2 = Cells(18, x)
    If x < 11 And x > 2 Then
        Select Case y
            Case 6 To 15
                If IsNumeric(z1) Then
                    If z1 = 0.5 Then
                        ActiveCell.Value = DateAdd("d", 14, Range("E31"))
                    Else
                        ActiveCell.Value = DateAdd("m", z1, Range("E31"))
                    End If
                End If
                Exit Sub
            Case 20 To 29
                If IsNumeric(z2) Then
                    If z2 = 0.5 Then
                        ActiveCell.Value = DateAdd("d", 14, Range("E31"))
                    Else
                        ActiveCell.Value = DateAdd("m", z2, Range("E31"))
                    End If
                End If
                Exit Sub
        End Select
    End If
    MsgBox ("The selected cell can not be updated this way")
    End Sub
    Office 2010/365

  10. #10
    Board Regular
    Join Date
    Aug 2002
    Location
    Australia
    Posts
    583
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Works Great. Thankyou very much

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •