Can anyone here write VBA, in relation to excel?

[eatgoodfood]

Im working on a project in excel and writing macros in VBA, I have an IF THEN statement, and every time I try to get the IF statement to perform more than one action, I get errors. If anyone here is familiar with excel and VBA, ill post the code and maybe you can figure out what im doing wrong.

[Chkadii]

Im working on a project in excel and writing macros in VBA, I have an IF THEN statement, and every time I try to get the IF statement to perform more than one action, I get errors. If anyone here is familiar with excel and VBA, ill post the code and maybe you can figure out what im doing wrong.

I think it depends on what version you're using, and in my bigger workbooks I've had to break the longer formulas down into smaller steps. What are you trying to do with this formula?

[Kodieh]

Are you sure you're using those statements correctly? Are you sure you're not meaning to use an if else? Or are you just using an if statement? Well, an if statement would still be an if then I've just never heard it called a full if then.

If(condition) {
.
.
.

}

It shouldn't throw errors, unless you're doing something incorrect inside the loop. Try trying each individual command by itself in the if statement, if they all work I'm wrong. If you can use all but one, then you have messed up that command.


I'm more C/C++ Java trained, but syntax is the only real thing that's different.

Sent from my SAMSUNG Galaxy SIII using Tapatalk 2

[eatgoodfood]

Sub RemoveAnimal()
Dim AnimalID As Variant
Dim FindAnimal As Range
Dim ws As Worksheet
Dim sName As String

Set ws = Worksheets("Index")
sName = InputBox(Prompt:="Enter Name of Animal to Delete", _
Title:="Remove Animal")

If sName = "" Then Exit Sub

With ws.Range("A2:A" & ws.Rows.Count)
Set FindAnimal = .Find(sName, , , xlWhole)
If Not FindAnimal Is Nothing Then

MsgBox "Clicking yes will permanently delete " & FindAnimal & " and its associated data.", _
vbYesNo, "Warning"

If vbYes Then FindAnimal.EntireRow.Delete
Sheets("FindAnimal").Delete

Else
MsgBox Prompt:="Animal Not Found, Unable to Delete.", Title:="Failed"
Exit Sub
End If
End With
Application.ScreenUpdating = True

End Sub

That is the code, what is in bold is the area im having trouble with. It will delete the entire row that the search function finds, but it errors on the part where I want it to delete the sheet. I have tried other code such as having it select the sheet first, then delete the active sheet, if I dont write the If Then statement as you see it there, It wont even run the macro. I am not very good at this stuff, I am just learning.

[Kodieh]

Have you tried moving the sheet command onto the same line as the if statement? Using the syntax that creates an if this then this AND this?

Sent from my SAMSUNG Galaxy SIII using Tapatalk 2

[eatgoodfood]

Yes I have, and the compiler wont even recognize it. As soon as I do it I get a compiler error.

[Kodieh]

A little digging showed me a clear all command

Sheet1.Cells.Clear

So, it seems to me you should try

FindAnimal.Cells.Clear

Now this won't delete the sheet, but just clear everything off of it. Perhaps there's an EntireSheet like EntireRow? Just seems like the syntax is incorrect when you try to delete the sheet.

Sent from my SAMSUNG Galaxy SIII using Tapatalk 2

[eatgoodfood]

I have found that the issue lies somehow with my variable FindAnimal, possibly how its designated, I am unsure. When I replace Sheets("FindAnimal").Delete with Sheets("Test").Delete it functions perfectly. Test being the name of the Animal I am using to test the code.

[eatgoodfood]

I figured it out, it did not like the variable. So when I change it to sName which is a string and is the user input, it functions perfect, here is the finished code.


Sub RemoveAnimal()
Dim AnimalID As Variant
Dim FindAnimal As Range
Dim ws As Worksheet
Dim sName As String

Set ws = Worksheets("Index")
sName = InputBox(Prompt:="Enter Name of Animal to Delete", _
Title:="Remove Animal")

If sName = "" Then Exit Sub

With ws.Range("A2:A" & ws.Rows.Count)
Set FindAnimal = .Find(sName, , , xlWhole)
If Not FindAnimal Is Nothing Then

Application.ScreenUpdating = True
DLT = MsgBox("Clicking yes will permanently delete " & FindAnimal & " and its associated data.", _
vbYesNo, "Warning")

If DLT = vbYes Then
FindAnimal.EntireRow.Delete
Application.DisplayAlerts = False
Worksheets(sName).Delete
Application.DisplayAlerts = True
Else
Exit Sub
End If


Else
MsgBox Prompt:="Animal Not Found, Unable to Delete.", Title:="Failed"
Exit Sub
End If
End With
Application.ScreenUpdating = True

End Sub

[Kodieh]

Ah, so you just needed to define it rather than try an give it an instance variable.

Good job!

Sent from my SAMSUNG Galaxy SIII using Tapatalk 2