Application.Match issue in Access VBA Module 1

[BigFish69]

The code below works fine in a XL VBA module but when I add it to my Access VBA module it produces an error. I suspect it needs to be modified but am unsure what to change.

Can someone point me in the right direction?

x = objWorkbook.Worksheets(lngCount).Application.Match("[All Markets]", Range("1:1"), 0)
y = objWorkbook.Worksheets(lngCount).Application.Match("[All Products]", Range("1:1"), 0)
objWorkbook.Worksheets(lngCount).Range(Cells(x).Offset(, 1), Cells.Offset(, -1)).EntireColumn.Delete

 

[PHV]

x = objWorkbook.Worksheets(lngCount).Application.Match("[All Markets]", [!]objWorkbook.Worksheets(lngCount).[/!]Range("1:1"), 0)
y = objWorkbook.Worksheets(lngCount).Application.Match("[All Products]", [!]objWorkbook.Worksheets(lngCount).[/!]Range("1:1"), 0)
objWorkbook.Worksheets(lngCount).Range(Cells(x).Offset(, 1), [!]objWorkbook.Worksheets(lngCount).[/!]Cells.Offset(, -1)).EntireColumn.Delete

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigFish69]

PH - I see the appropriate values in the first two lines for X and Y. The values are 8 and 12. But something must not quite be right in the 3rd line as it produces an application or object defined error. I have both x and y dim as long, is this what I should be using for the dim?

Thanks for your help!

 

[PHV]

Sorry for the incomplete reply:
objWorkbook.Worksheets(lngCount).Range(objWorkbook.Worksheets(lngCount).Cells(x).Offset(, 1), objWorkbook.Worksheets(lngCount).Cells.Offset(, -1)).EntireColumn.Delete

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BigFish69]

PHV - Thank you for your help!

Do you know what the correct code would be to transform this line that works in Excel to Access VBA?

Columns("C:C").Select
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight

I currently have the following but it does not work.

objWorkbook.Worksheets(lngCount).Range("C1:C1").Select
Selection.Cut
objWorkbook.Worksheets(lngCount).Range("B1:B1").Select
Selection.Insert Shift:=xlToRight

 

[PHV]

objWorkbook.Worksheets(lngCount).Range("C1:C1").Cut
objWorkbook.Worksheets(lngCount).Range("B1:B1").Insert Shift:=-4161 'xlToRight

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886