CODEWITHSUNDEEP
vbaexcelsearch
Divin3
Divin3

Reputation: 548

Excel VBA this should be working but it's not

What I want to do, is when I save my excel file, I want the script to check for all the cells that has the "$" as last character and check if there is a cell with the name "Backup" in the same column as it found a cell with , and if there is, only then apply my script

Here is my code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim c As Range
Dim x As Integer
Dim y As Integer
Dim isbackup As Boolean
isbackup = False

For Each c In Range("A1:N60")
If Right(c, 1) = "$" Then y = c.Column: x = c.Row
    For i = 1 To 60
        If Cells(i, y).Value = "Backup" Then
            isbackup = True
            Exit For
        End If
    Next i
If isbackup = True And Right(c, 1) = "$" Then <my script>
Next c

I don't see any syntax or logical errors, but I get the error code

1004: "application-defined or object-defined error"

Upvotes: 0

Views: 76

Answers (1)

simpLE MAn
simpLE MAn

Reputation: 1622

Maybe this could help:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim c As Range
    Dim x As Integer, y As Integer
    Dim isbackup As Boolean
    isbackup = False

    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets(1).Range("A1:N60")

    For Each c In rng

        If Right(c, 1) = "$" Then
            y = c.Column: x = c.Row

            Dim i As Integer
            For i = 1 To rng.Rows.Count '--> I find this more flexible
                If Cells(i, y).Value = "Backup" Then
                    isbackup = True
                    Exit For
                End If
            Next i

        End If

        If isbackup = True And Right(c, 1) = "$" Then DoEvents 'Replaced by your script

    Next c
End Sub

Upvotes: 1

Related Questions