Basic Excel VBA Programming Question?

I'm having trouble to what I think is a very simple excel macro. I havent had any real experience with MS VB or excel coding. I need a macro that deletes the word "sent" in a certain column.

Not Delete the column! just the values and only delete the values if the column next to it says "No" It needs to be able to loop through all my values.

Heres what I have so far I dont know how close or far I am.

Dim Cell As Range

Dim Clear As String

Application.ScreenUpdating = False

Clear = "Nothing"

' On Error GoTo cleanup

For Each Cell In Sheets("Sheet1").Columns("D").Cells.SpecialCells(xlCellTypeConstants)

'If Cell.Value = "sent" And LCase(Cell.Offset(0, -1).Value) = "NO" _

Then Cell.Offset(0, 0).Value = "Blank"

If Cell.Value = sent Then GoTo cleanup

Next Cell

cleanup:

Cell.Value = "Clear"

MsgBox "Update Complete"

Application.ScreenUpdating = True

End Sub

2 Answers

Relevance
  • 1 decade ago
    Best Answer

    Have no file in hand, some code of yours not readable, please backup your file before you try the following

    Dim Cell As Range

    Application.ScreenUpdating = False

    For Each Cell In Sheets( "Sheet1").Columns( 4).Cells.SpecialCells( xlCellTypeVisible)

    If LCase( Cell.Value) = "sent" And LCase( Cell.Offset( 0, -1).Value) = "no" Then Cell.Value = ""

    Next Cell

    Application.ScreenUpdating = True

    MsgBox "Update Complete"

    End Sub

    Please contact for more details

  • devick
    Lv 4
    3 years ago

    pay attention to what person Devilish instructed you - do no longer attempt to income VB.internet or perhaps VB6 and then attempt to persist with that to VBA - it would not artwork. VBA is a stripped-down version of VB6 that has extremely some quirks and distinctive helpful properties of its very own. you are able to grow to be an remarkable programmer in VB6 and then change to VBA and be definitely stumped because of the fact issues are so distinctive, and straightforward projects grow to be extra reliable. it can be a waste of a while to first learn seen uncomplicated, then change to seen uncomplicated for applications and in no way be waiting to persist with the tricks and recommendations you found out from VB6/.internet to VBA. I in fact self-taught VBA, yet I even have ordinary computer programming understanding to returned me. i've got merely spent extremely some time on Google and finding by way of code banks for workarounds, code snippets, errors comments etc. interior the supplies, i've got indexed a number of the internet sites I shop going returned to for suggestions and help. (those in all possibility won't show you how to learn, yet they are going to be waiting to help your "How do I try this...?" type questions) you will no longer locate extensive walkthoughs for "gaining knowledge of VBA" on line... maximum sites will inform you the thank you to do one ingredient or yet another, yet they won't thoroughly walk you by way of the approach of gaining knowledge of and then turning out to be an expert. The books that Devilish pronounced look promising. reliable success!

Still have questions? Get your answers by asking now.