VBA to find cell by name (text) and select the cell above it

[wzthxj]

I'm slowly putting together a workbook and need to create a column currency total above a specific cell. The column number will change due to multiple users, but it will always be found in row A. This is the most recent code I've used but I get an Object variable or With block variable not set error. I tried adding With/End With but it didn't like that either. Appreciate any help as this is my last step to complete this project. Thanks.

Sub FindEx1()
Range("B2:BL2").Find("Potential Debit Amt").Select
ActiveCell.Offset(-1, 0).Select
End Sub

 

[maabadi]

Try this:

Cells.Find(What:="Potential Debit Amt", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(-1, 0).Activate

 

[wzthxj]

Thanks for the reply but I received the same error message and the entire block was highlighted in yellow.

 

[maabadi]

Please upload your code complete.
Are you have With section on your code Without End With?

Or Are you have these Words at your file at all. Maybe you have wrong spelling when writing?

 

[Marc L]

but I get an Object variable or With block variable not set error.

It's what happens when nothing is found ! Maybe the space is not a space !​

See the VBA help of Range.Find method in order to check if something matches …​

As you can try to operate manually and once it works operate the same with the Macro Recorder activated.​

Another way is to use the worksheet function MATCH.​

For further help attach your workbook via a files hosting website …​

 

[wzthxj]

This is what I have so far. It started as some separate macros that I've been working to assemble and fine tune. I have very little experience using VBA so I'm sure this is sloppy. But everything is working up until this point. Thank you for your time.

Sub FormatReviewClaimsWorksheet()
'
' FormatReviewClaimsWorksheet Macro
'

'
    Rows("1:1").Select
    Selection.Font.Bold = True
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1:1").Select
    ActiveWindow.FreezePanes = True
    Call AddColumnsWithHeadersResize
    Range("A1").EntireRow.Insert
    Call FindEx1
    Selection.NumberFormat = "$#,##0.00"
    ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[3999]C)"
    Call FindEx2
    Selection.NumberFormat = "$#,##0.00"
    ActiveCell.FormulaR1C1 = "=SUM(R[2]C:R[3999]C)"
    Selection.FormatConditions.Add Type:=xlExpression, Formula1:= _
        "=COUNTA($A1:$AL4000)>0"
    Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
    With Selection.FormatConditions(1).Borders(xlLeft)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlRight)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlTop)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.FormatConditions(1).Borders(xlBottom)
        .LineStyle = xlContinuous
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Selection.FormatConditions(1).StopIfTrue = False
    Rows("3:4000").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub
Sub MoveData()
   
    Sheets("Exp Trend_Repair Grp").Select
    Rows("2:2").Select
    Selection.Insert Shift:=xlDown
    Range("G2").Select
    ActiveCell.FormulaR1C1 = "=SUBSTITUTE(R[1]C,""USD"","""")+0"
    Range("G2").Select
    Selection.AutoFill Destination:=Range("G2:K2"), Type:=xlFillDefault
    Range("G2:K2").Select
    Selection.Copy
    Sheets("Data").Select
    Range("E4:I4").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Sheets("Review Summary").Select
    Range("E12:F12").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=Data!R[-8]C"
    Range("E13:F13").Select
    ActiveCell.FormulaR1C1 = "=Data!R[-9]C[1]"
    Range("E14:F14").Select
    ActiveCell.FormulaR1C1 = "=Data!R[-10]C[2]"
    Range("E15:F15").Select
    ActiveCell.FormulaR1C1 = "=Data!R[-11]C[3]"
    Range("E16:F16").Select
    ActiveCell.FormulaR1C1 = "=Data!R[-12]C[4]"
    Range("E17:F17").Select
    Sheets("Exp Trend_Repair Grp").Select
    Rows("2:2").Select
    Selection.Delete Shift:=xlUp
    Sheets("Review Summary").Select
    Range("F3:K3").Select
    ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-1]C[-3]"
    Range("F4:J4").Select
    ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-2]C[7]"
    Range("K4").Select
    ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-2]C[3]"
    Range("F5:G5").Select
    ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-3]C[-5]"
    Range("F7:K7").Select
    Sheets("Svc Agent Criteria").Select
    Range("V2").Select
    Sheets("Review Summary").Select
    Range("F7:K7").Select
    ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-5]C[16]"
    Range("E18:F18").Select
    Sheets("Svc Agent Criteria").Select
    Range("G2").Select
    Sheets("Review Summary").Select
    ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-16]C[2]"
    Range("E29:F29").Select
    ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-27]C[15]"
    Range("E30:F30").Select
    ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-28]C[18]"
    Range("E36:F36").Select
    ActiveCell.FormulaR1C1 = "='Svc Agent Criteria'!R[-34]C[3]"
    Range("E31:F31").Select
    ActiveCell.FormulaR1C1 = "='Exp Trend_Repair Grp'!R[-29]C[14]"
    Range("E32:F32").Select
    ActiveCell.FormulaR1C1 = "='Exp Trend_Repair Grp'!R[-30]C[12]"
    Range("E33").Select
End Sub
Sub FindEx1()
Cells.Find(What:="Potential Debit Amt", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(-1, 0).Activate
End Sub
Sub FindEx2()
Cells.Find(What:="Debit Amt", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(-1, 0).Activate
End Sub

 

[Fluff]

Aren't your headers called "Potential Debit Amount" & "Debit Amount" or have you changed them?

 

[wzthxj]

You are correct, and that was the problem. Sorry to have created extra work for you Maabadi, and thank you very much.

 

[Fluff]

Glad we could help & thanks for the feedback.