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
atljarmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FaustulusCommented:
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

0
atljarmanAuthor Commented:
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.
0
FaustulusCommented:
Perhaps this could work for you?
Option Explicit

Private Sub test()

    ' Match formats to field names
    Const AllFields As String = "Doc2|Doc3|Doc5"
    Const AllFormats As String = "0|0.00|0"
    
    Dim MyField() As String
    Dim MaxRow As Long
    Dim i As Integer
    
    MaxRow = 12                     ' set as required
    MyField = Split(AllFields, "|")
    For i = LBound(MyField) To UBound(MyField)
        SetCellFormat MyField(i), Split(AllFormats, "|")(i), MaxRow
    Next i
End Sub

Private Sub SetCellFormat(ByVal MyField As String, _
                          ByVal NumFormat As String, _
                          ByVal MaxRow As Long)

    Dim Fnd As Range
    
    With ActiveSheet
        Set Fnd = .Cells.Find(What:=MyField, SearchDirection:=xlNext, SearchOrder:=xlByColumns)
        If Not Fnd Is Nothing Then
            With .Columns(Fnd.Column)
                Range(.Cells(1), .Cells(MaxRow + 1)).NumberFormat = NumFormat
            End With
        End If
    End With
End Sub

Open in new window

Please observe that the leading period in this line,
Range(.Cells(1), .Cells(MaxRow + 1)).NumberFormat = NumFormat
has now been omitted. My earlier post was faulty in that respect.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
atljarmanAuthor Commented:
Thanks it worked
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.