Replacing IF function with VBA

Hi all,

I am putting together a spreadsheet to track equipment and would like to remove the "IF" function that is used in column N due to nested restrictions.

I've searched the forum but can't seem to find a VBA that suits, or is easily deciphered by a newbie like me.

Is there a VBA that would achieve this, i.e. =IF(B5=$Q$5,M5+1095, IF(B5=$Q$6,M5+730, IF(B5=$Q$7,M5+730, IF(B5=$Q$8,M5+1825, IF(B5=$Q$9,M5+1825, IF(B5=$Q$10,M5+182, IF(B5=$Q$11,M5+730, IF(B5=$Q$12,M5+730)))))))) and still allow to addition items to be added to column Q?

The +X in the formula above relates to "Test date due" frequency which varies depending on equipment.

Any advise would be greatly appreciated.

Grant

Hi -

in your sheet event;

Please Login or Register  to view this content.

event

Thanks event, that's exactly what I was after.

Much appreciated.

The only thing is I used to track how many times an item was despatched, see below-

Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Integer
Dim col As Integer
row = Target.row
col = Target.Column

If col = 6 Then
Module1.total_change = Worksheets("Sheet1").Cells(row, 8).Value
If Target = Worksheets("Sheet1").Cells(row, 6) Then
Module1.total_change = Module1.total_change + 1
Worksheets("Sheet1").Cells(row, 8) = Module1.total_change
End If
End If

End Sub


How do I return this function?

Hi -

Just add it on the worksheet change event either before or after the codes I have.

PS: Please add CODE tags as well in all your post.

event

Thanks for your quick reply.

I've added it to the worksheet change and it flags an error-

Compile error:
Ambiguous name detected: Worksheet_Change

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
If Target.Column = 13 And Target.row >= 5 Then
With Columns(17)
Set c = .Find(Cells(Target.row, 2).Value, , , 1)
If Not c Is Nothing Then
Target.Offset(, 1).Value = Target.Value + c(, 3)
End If
End With
End If
If Target.Column = 2 And Cells(Target.row, 13) <> vbNullString Then
With Columns(17)
Set c = .Find(Target.Value, , , 1)
If Not c Is Nothing Then
Cells(Target.row, 14).Value = Cells(Target.row, 13).Value + c(, 3)
End If
End With
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim row As Integer
Dim col As Integer
row = Target.row
col = Target.Column

If col = 6 Then
Module1.total_change = Worksheets("Sheet1").Cells(row, 8).Value
If Target = Worksheets("Sheet1").Cells(row, 6) Then
Module1.total_change = Module1.total_change + 1
Worksheets("Sheet1").Cells(row, 8) = Module1.total_change
End If
End If

End Sub

I assume that I'm doing it wrong?

Apologies, I'm very new at this and still trying to get my head around it.

Grant

Sorry event.

I had a moment, got it sorted.

Thanks again.