Using the find In VBA

Anonymous

I am trying to write a macro that will deduct an item from my stock (which is housed in a different workbook) when I hit the complete button. My thoughts were to use the find function, activate the cell, and then subtract the quantity from the active cell. I am just starting with the macro, but I am running into an object error with my find.

Sub Deduct()

Dim C As String

'Deduct the Quantity from Stock

Workbooks.Open "C:\Users\furnd\Desktop\Test Deduct.xlsm"

With Workbooks("Test Deduct.xlsm").Sheets("Sheet1").Range("A1:A2000")

Set C = .Find(What:="A1").Value

End With

Workbooks("Test Deduct.xlsm").Activate

Sheets("Sheet1").Select

C.Offset(0, 1).Select

End Sub

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

47 answers

Anonymous

2023-03-10T17:05:17+00:00

Jeovany,

I am running into an issue when tryin to save the document as a pdf. I have been looking to see if I can find a fix for it, but I am having trouble. The invoice that you created will save as 1 page in a PDF, but the invoice that I created will print on 1 page, but when saving it as a pdf it will not save as 1 page. Below are some screenshots that show the issue.

Here is the code I am using.

Dim saveLocation As String

Dealer = Range("D5")

Invoice = Range("D2")

saveLocation = "\TIMS-COMPUTER\Users\furnd\POS System\Invoices" & Dealer & "" & Invoice & ".pdf"

'Save Active Sheet(s) as PDF

ActiveSheet.ExportAsFixedFormat xlTypePDF, saveLocation

Here are my margins.

Here is the screen in excel that will print as 1 page.

Here is how it looks when saving to PDF.
Was this answer helpful?

0 comments No comments
Anonymous

2023-03-22T19:20:12+00:00

Andreas,

I was wondering if you could help me out on a coding question. I have been working on this project for a bit and thanks to the help that you and Jeovany have given me, I am getting close to having a working model. I am running into one issue. I need have code that opens another workbook. example below.

Set Ledwb = Workbooks.Open _

("\TIMS-COMPUTER\Users\furnd\POS System\Ledger.xlsm")

In a perfect world, no one should be in this workbook, but I know that could cause an error if someone is in it. Is there a way to write code that will throw and Error saying "Ledger.xlsm could not open" if the file is open elsewhere?
Was this answer helpful?

0 comments No comments
Anonymous

2023-03-23T08:52:34+00:00

Hi Tim

Good morning

From the pictures I would say it is a case of the columns width adjustment

1st Try by narrowing down the Item description column

2nd if necessary

On the layout ribbon tab.>> Paper Margin>> Select Narrow

Once you do that the PDF macro and print should match.

Do let me know if you need more help.

Regards

Jeovany
Was this answer helpful?

0 comments No comments
Anonymous

2023-03-23T12:39:49+00:00

Thanks Jeovany,

I actually got that one figured out a while ago and forgot I had posted that.

I am however stuck with the question I asked Andreas. Do you know of anyway to do what I am looking for?

What I really want to do is at the start of my complete macro, open all the workbooks that I will need to make sure they are available to write to them. If one of them doesn't open I want to throw an error saying it didn't open. and stop the code until we can find out who has it open.
Was this answer helpful?

0 comments No comments
Anonymous

2023-03-23T15:36:59+00:00

Jeovany,

I am trying to use Power Query to pull data off of a PDF and put it in my workbook to add a truck to the inventory. but every time I try and load the data it adds 2 columns that causes the data to not be where I need it to be. Picture below.

The first picture is the screen before I load the data and the second screen is what happens when I try and load it to cell A4. Am I doing something wrong or is there any way around this?
Was this answer helpful?

0 comments No comments