Excel Macro Help

[pjbrazendale]

Hi Guys,

Looking for some help from any excel gurus out there. What I need to do is create a macro then when for example i enter the number 10 in cell A5 it automatically takes the value 10 and divides it by 3.76 and puts the resultant value in the same cell. Not worked with excel since school so any help would be appreciated.

Many Thanks

[danch]

Pete,
Is it a specific cell all the time or any cell in the worksheet?

[pjbrazendale]

Re: Excel Macro Help

Pete,
Is it a specific cell all the time or any cell in the worksheet?

Hi mate its a range of cells A5:A10 in a worksheet.

Cheers

[danch]

Put this in the Workbook_SheetChange event in the VBA Editor :

On Error GoTo Error_Out
With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If Not Intersect(Target, Target) Is Nothing Then
Application.EnableEvents = False

Target.Value = Target.Value / 3.76

End If

Continue_On:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Exit Sub

Error_Out:
MsgBox Err.Description
Resume Continue_On

[danch]

Sorry missed your reply, use this for those specific cells :

On Error GoTo Error_Out

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

If Not Intersect(Target, Range("A5:A10")) Is Nothing Then
Application.EnableEvents = False

Target.Value = Target.Value / 3.76

End If

Continue_On:
With Application
.ScreenUpdating = True
.EnableEvents = True
End With
Exit Sub

Error_Out:
MsgBox Err.Description
Resume Continue_On

[danch]

This will divide any cell in the A5:A10 range by 3.76 & will work on all the worksheets in the workbook

Dan

[pjbrazendale]

Re: Excel Macro Help

This will divide any cell in the A5:A10 range by 3.76 & will work on all the worksheets in the workbook

Dan

Hi Mate,

Thanks for the reply I have pasted the code into my worksheet which is one of several in the workbook however it does not compile it just says 'label not defined' on the first line of code.

Cheers

[Gecko501]

Excel Macro Help

Does it really need to be a macro? I would use two separate tabs. Tab 1 has the original values in range A5:A15 and tab 2 has the same formatting but has formulas in the range referring to the original data. (ie: =sheet1!A5/3.75)

[danch]

If you Alt + F11 , then double click on the left 'This Workbook' you should then get 2 dropdown options at the top of the screen, drop the left one down to say 'Workbook' & then the right one to say SheetChange.

This should then give you :

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


End Sub

Paste my code in between the Private Sub & End Sub

Also
what version of excel are you using 2003/2007/2010 ?
Dan

[pjbrazendale]

Re: Excel Macro Help

If you Alt + F11 , then double click on the left 'This Workbook' you should then get 2 dropdown options at the top of the screen, drop the left one down to say 'Workbook' & then the right one to say SheetChange.

This should then give you :


Paste my code in between the Private Sub & End Sub

Also
what version of excel are you using 2003/2007/2010 ?
Dan

Hi Mate,

Tried that but the left drop down only gives me worksheet not workbook and I'm on excel 2003.

Cheers

[danch]

Try this

[danch]

Hi Mate,

Tried that but the left drop down only gives me worksheet not workbook and I'm on excel 2003.

Cheers

Ok, select 'Worksheet' & then 'Change'

& paste it in that Sub, will still work but just for the sheet you have selected.

Dan