VBA MS Access Row 1 to last column

Question

I'm trying to figure out why this code works every other time I run it in Access.

I have a spreadsheet that is being built via access query and then exported to Excel. Then I am running this to do a little formatting/cleanup. (It's suppose to select the cell in row 1 copy those values to the last column (in row 1) and then paste them in a specified cell.

It runs perfectly on the first run but I get a debug error if I run the same thing again. Debug comes up on this line: .Range(ActiveCell, ActiveCell.END(xlUp).END(xlToRight)).Copy.

The exact error is "Object Variable or With block variable not set"

Any help would be greatly appreciated. THANKS!

Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim rng as Range

With myWorkbook.Worksheets("qryAutoBuildFile")
myWorkbook.Sheets("qryAutoBuildFile").Activate
Set rng = Range("A1:CU1").Find("Article").Offset(0, 1)

     rng.Select
    .Range(rng, rng.END(xlUp).END(xlToRight)).Copy
    .Range("A1:CU1").Find("Expr1008").PasteSpecial xlPasteValues
    .Range("A1").Select
End With
    appExcel.CutCopyMode = False

Start with applying the advice [from here](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) — chris neilsen, Aug 31 '20 at 22:43
@chrisneilsen yeah I know using Select isn't a great way of doing things. Normally I don't but in this case I don't know the syntax/function to combine both my lines to just copy directly what I want. If I try to use `.Range("A1:CU1").Find("Article").Offset(0, 1).copy` it won't select the exact cells I need and I need specific ones selected. Any thoughts on combining them? — Deke, Aug 31 '20 at 22:54
What happens if you prefix `ActiveCell` in both places with dots? `.Range(.ActiveCell, .ActiveCell.END(xlUp).END(xlToRight)).Copy` — HansUp, Aug 31 '20 at 23:00
Dim a Range variable, Set that to the result of the Find . Substitute ActiveCell with that variable. — chris neilsen, Aug 31 '20 at 23:04
@HansUp had thought of that but get the same error. Good call though. — Deke, Aug 31 '20 at 23:05
@chrisneilsen I setup a range variable like you suggested but I get the error "Method 'Range' of Object' _global failed". See updated code in my original post. The error pops on `Set rng = Range("A1:CU1").Find("Article").Offset(0, 1) ` — Deke, Aug 31 '20 at 23:15
That would suggest "Article" was not found. Try `Set Rng = .Range("A1:CU1").Find("Article")` `If Not Rng Is Nothing Then` `Set Rng = Rng.Offset(1, 0)` `Else` `MsgBox("Arcticle Not Found")` `End If` — chris neilsen, Aug 31 '20 at 23:23
@chrisneilsen your first suggestion was good. I just needed to define it a little more specifically. See my answer below. I added the workbook to the range variable and that cleared up the issue. It's all good now. Thanks for your help! — Deke, Aug 31 '20 at 23:27

score 1 · Answer 1 · answered Aug 31 '20 at 23:41

There are several other issues with your code too.

Some parameters of Find require explicit setting. See here
Use the With block
Account for possibility Find returns Nothing, twice

Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim rng as Excel.Range
Dim rngDest as Excel.Range

With myWorkbook.Worksheets("qryAutoBuildFile")
    '.Activate  ' use the With block, not really needed 
    Set rng = .Range("A1:CU1").Find(What:="Article", & _
      LookIn:=xlValues, & _
      LookAt:=xlWhole, & _
      SearchOrder:=xlByRows)
      ' Adjust to suit your needs.  Add MatchByte as well is you require double-byte language support
    If rng is Nothing then
        ' Not found, what now?
    Else
        Set rng = .Range(rng.Offset(0, 1), rng.END(xlToRight)) ' a bit shorter
        Set rngDest = .Range("A1:CU1").Find("Expr1008") 
         ' Don't need to specify Find parameters again, that previously specified onews will be used (unless you want to change them)

        If rngDest Is Nothing Then
            ' Not found, what now?
        Else
            rngDest.Value2 = rng.Value
        End If
    End If
End With

score 0 · Answer 2 · answered Aug 31 '20 at 23:25

Dim appExcel As Excel.Application
Dim myWorkbook As Excel.Workbook
Dim rng as Range

With myWorkbook.Worksheets("qryAutoBuildFile")
myWorkbook.Sheets("qryAutoBuildFile").Activate
Set rng = myWorkbook.Worksheets("qryAutoBuildFile").Range("A1:CU1").Find("Article").Offset(0, 1)

     rng.Select
    .Range(rng, rng.END(xlUp).END(xlToRight)).Copy
    .Range("A1:CU1").Find("Expr1008").PasteSpecial xlPasteValues
    .Range("A1").Select
End With
    appExcel.CutCopyMode = False

VBA MS Access Row 1 to last column

2 Answers2