Having trouble selectively copying calculated values to a Column

[cjpaul]

Good day to all. I would like to get calculated fields to automatically update fields on the data entry page and am having trouble. I have a sheet (Sheet1) with Columns E, F, G which correspond to Height, Width and Depth in Feet. The range is E5:G105. I have corresponding Columns H, I and J which correspond to the same headings but in Meters.

I created a second Tab (Sheet2) where I copied the same 6 columns and use IF statements to calculate data based on information entered on Sheet1 for instance if they enter 1 in cell E5 I then calculate the Metric equivalent on Sheet2 in the corresponding Metric Column H. This all works wonderfully but i want to copy the missing data (either the English or Metric counterpart to what they entered) back to Sheet1 (the data entry sheet) and I am having a problem. Unfortunately, we can get a mixture of both metric and English measurements on the same project so it is not as simple as entering either all English or metric and copying the other back.

I would prefer to do this dynamically as they enter the data but could if I need to, use a single button for completing this task once all entry was finished.

Any help would be greatly appreciated.

 

[AlphaFrog]

Right-click on the Sheet1 tab and select View Code in the pop-up menu
Paste the code below in the VBA edit window.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    On Error GoTo ReEnable
    If Not Intersect(Range("E5:G105"), Target) Is Nothing Then
        ' Feet to Meters
        If IsNumeric(Target.Value) Then Target.Offset(, 3).Value = Target.Value * 0.3048
    ElseIf Not Intersect(Range("H5:J105"), Target) Is Nothing Then
        ' Meters to Feet
        If IsNumeric(Target.Value) Then Target.Offset(, -3).Value = Target.Value * 3.2808399
    End If
ReEnable:
    Application.EnableEvents = True
    
End Sub

 

[cjpaul]

That is spectacular, thank you so much

 

[cjpaul]

That is spectacular, thank you so much

This code worked perfectly and i would like to add columns K5:K105 (Volume by gallons) and L5:L105 (Volume by Liters) to the dynamic calculations. I have tried to add the additional code to reflect this but it does not calculate when one or the other data is entered in either column. here is what i did:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ReEnable
If Not Intersect(Range("E5:G105"), Target) Is Nothing Then
' Feet to Meters
If IsNumeric(Target.Value) Then Target.Offset(, 3).Value = Target.Value * 0.3048
ElseIf Not Intersect(Range("H5:J105"), Target) Is Nothing Then
' Meters to Feet
If IsNumeric(Target.Value) Then Target.Offset(, -3).Value = Target.Value * 3.2808399
End If
If Not Intersect(Range("K5:K105"), Target) Is Nothing Then
' Gallons to Liters
If IsNumeric(Target.Value) Then Target.Offset(, 1).Value = Target.Value * 3.7854
ElseIf Not Intersect(Range("L5:L105"), Target) Is Nothing Then
' Liters to Gallons
If IsNumeric(Target.Value) Then Target.Offset(, -1).Value = Target.Value / 3.7854
ReEnable:
Application.EnableEvents = True

End Sub

once again any help would be greatly appreciated

 

[cjpaul]

This code worked perfectly and i would like to add columns K5:K105 (Volume by gallons) and L5:L105 (Volume by Liters) to the dynamic calculations. I have tried to add the additional code to reflect this but it does not calculate when one or the other data is entered in either column. here is what i did:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
On Error GoTo ReEnable
If Not Intersect(Range("E5:G105"), Target) Is Nothing Then
' Feet to Meters
If IsNumeric(Target.Value) Then Target.Offset(, 3).Value = Target.Value * 0.3048
ElseIf Not Intersect(Range("H5:J105"), Target) Is Nothing Then
' Meters to Feet
If IsNumeric(Target.Value) Then Target.Offset(, -3).Value = Target.Value * 3.2808399
End If
If Not Intersect(Range("K5:K105"), Target) Is Nothing Then
' Gallons to Liters
If IsNumeric(Target.Value) Then Target.Offset(, 1).Value = Target.Value * 3.7854
ElseIf Not Intersect(Range("L5:L105"), Target) Is Nothing Then
' Liters to Gallons
If IsNumeric(Target.Value) Then Target.Offset(, -1).Value = Target.Value / 3.7854
ReEnable:
Application.EnableEvents = True

End Sub

once again any help would be greatly appreciated

Sorry, i also added End If
End If before End Sub

 

[AlphaFrog]

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Application.EnableEvents = False
    On Error GoTo ReEnable
    
    If IsNumeric(Target.Value) Then
        If Not Intersect(Range("E5:G105"), Target) Is Nothing Then
            ' Feet to Meters
            Target.Offset(, 3).Value = Target.Value * 0.3048
        ElseIf Not Intersect(Range("H5:J105"), Target) Is Nothing Then
            ' Meters to Feet
            Target.Offset(, -3).Value = Target.Value * 3.2808399
        ElseIf Not Intersect(Range("K5:K105"), Target) Is Nothing Then
            ' Gallons to Liters
            Target.Offset(, 1).Value = Target.Value * 3.7854
        ElseIf Not Intersect(Range("L5:L105"), Target) Is Nothing Then
            ' Liters to Gallons
           Target.Offset(, -1).Value = Target.Value / 3.7854
        End If
    End If
    
ReEnable:
    Application.EnableEvents = True
    
End Sub

 

[cjpaul]

Thank you, greatly appreciated.