[RESOLVED] [EXCEL] Convert =IF(G2="","",G2/1.1) to VBA

[Lovelylou79]

Hi There,

I have a workbook that will be shared by a range of users with different levels of exposure to excel. I need the calculation =IF(G2="","",G2/1.1) for all of colomn H if there is a value in G, but I need it to be hidden so noone can "accidentally" delete it. I cannot protect the sheet to hide it because the sheet is constantly being manipulated.

I have the following, which works in a test sheet to calculate GST (I actually want it to remove the GST amount from the whole number), but not in the actual workbook -

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range, cell As Range
On Error GoTo exit_here
Set rng = Application.Intersect(Target, Range("A:A"))
If Not rng Is Nothing Then
Application.EnableEvents = False
For Each cell In rng
Select Case cell.Column
Case 1
If cell.Value > 0 Then
cell.Offset(, 1).Value = cell.Value * 0.11
End If
Case 2
If cell.Value < 0 Then
'nothing
End If
End Select
Next cell
End If
Application.EnableEvents = True
Exit Sub
exit_here:
Application.EnableEvents = True
End Sub

Please help!!

TA

[westconn1]

the easy way to do the calculation is when the value in column G changes, update H

Private Sub Worksheet_Change(ByVal Target As Range)
 
if target.column = 7 then  'column G
  if not isempty(target) then 
     target.offset(,1) = target / 1.1
    else
     target.offset(,1) = ""
  end if
end if
 
End Sub

[Lovelylou79]

westconn1, Thank you so much, this works beautifully!
However, when i delete the values in "G" in bulk, I get a Run-Time error "13", Type Mismatch. How do I stop that?

[westconn1]

try like, but i have not tested

Private Sub Worksheet_Change(ByVal Target As Range)
    for each cel in target.cells 
    if cel.column = 7 then  'column G
      if not isempty(cel) then
         cel.offset(,1) = target / 1.1
        else
         cel.offset(,1) = ""
      end if
    end if
    next
    End Sub