Avatar of atljarman
atljarman asked on

Modify open spreadsheet column if found in Access VBA

I have a function that creates a spreadsheet from a query object very nicely.  The problem is that a few of the columns are not formatted correctly.  I've added code to the function so that as the spreadsheet is being created it formats the column correctly.  Now if the column in the spreadsheet does not exist, it errors out.  I thought I added check code to see if the spreadsheet had a column name of the one I'm trying to format, and it doesn't work.  Any thoughts.

'This is where I'm trying to look for the column "Doc2"
myField = "Doc2"
If .Cells.Find(What:=myField, SearchDirection:=xlNext, SearchOrder:=xlByColumns) Is Nothing Then
    firstcol = .Cells.Find(What:=myField, SearchDirection:=xlNext, SearchOrder:=xlByColumns).Column
    .Range(.Cells(1, firstcol), .Cells(lngMaxRow + 1, firstcol)).NumberFormat = "0"
End If

Open in new window

I was trying code like If firstcol > 0, but i still had the same error:
91-Object variable or With block variable not set
Microsoft AccessMicrosoft ExcelVB Script

Avatar of undefined
Last Comment

8/22/2022 - Mon

Perhaps the problem really is what the man says, "With block variable not set". In the snippet you posted that variable is missing. It should be a Worksheet object. The following code works.
Option Explicit

Private Sub test()

    Dim myField As String
    Dim Fnd As Range
    Dim lngMaxRow As Long
    lngMaxRow = 12
    myField = "Doc2"
    With ActiveSheet
    'This is where I'm trying to look for the column "Doc2"
        Set Fnd = .Cells.Find(What:=myField, SearchDirection:=xlNext, SearchOrder:=xlByColumns)
        If Not Fnd Is Nothing Then
            With .Columns(Fnd.Column)
                .Range(.Cells(1), .Cells(lngMaxRow + 1)).NumberFormat = "0"
            End With
        End If
    End With
End Sub

Open in new window


Testing this now.  Is there a way to make your code a function or to loop through multiple column names something like:

myField = "Doc2,Doc3,Field1"

then loop through each of the names?  I have about 10 columns right now to search through most them are zero format, 2 are 6 digit, one is column width.  Almost seems like a function would work, but not sure how to implement.  If I did a function, I could build in a variable to tell it whether it was a format or a number format.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

Thanks it worked
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes