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

wzthxj

New Member
Joined
Mar 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,419
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
Try this:
VBA Code:
Cells.Find(What:="Potential Debit Amt", After:=ActiveCell, LookIn:=xlFormulas, LookAt:=xlPart, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Offset(-1, 0).Activate
 
Solution

wzthxj

New Member
Joined
Mar 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Thanks for the reply but I received the same error message and the entire block was highlighted in yellow.
 

maabadi

Well-known Member
Joined
Oct 22, 2012
Messages
2,419
Office Version
  1. 2019
  2. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Apr 5, 2021
Messages
1,263
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

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

New Member
Joined
Mar 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
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.

VBA Code:
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

wzthxj

New Member
Joined
Mar 29, 2021
Messages
12
Office Version
  1. 365
Platform
  1. Windows
You are correct, and that was the problem. Sorry to have created extra work for you Maabadi, and thank you very much.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
Glad we could help & thanks for the feedback.
 

Forum statistics

Threads
1,141,707
Messages
5,707,983
Members
421,539
Latest member
zuniBM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top