A quick dim cell thing

The Idea Dude

Well-known Member
Joined
Aug 15, 2002
Messages
591
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
Code:
 
Upvote 0
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
 
Upvote 0
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 :)
 
Upvote 0
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
 
Upvote 0
Hi Richie, Hot Peppers help worked for me :) Now my question relates to the .5 of a month :)

Thanks
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top