Link to home
Start Free TrialLog in
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
Else
    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
Avatar of Faustulus
Faustulus
Flag of Singapore image

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

Avatar of atljarman
atljarman

ASKER

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.
ASKER CERTIFIED SOLUTION
Avatar of Faustulus
Faustulus
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks it worked