OFFSET with variables

[Grace310]

I am trying to OFFSET from cell A1 based upon a variable in cell A2. The cell I need to OFFSET to is also located in column A, but it could always differ based upon the variable in A2. Here is the piece of code performing this OFFSET.

'The current activecell is A1

Please Login or Register  to view this content.

Originally, there was no error message; however, the cell was not offsetting (unless I changed the column reference, but it would move along Row 1, and never to another Row, which is the intent). Now I am getting a mismatch error. I tried using Find as well, but got the 1004 error. I am working across multiple spreadsheets, which may be part of the issue.

Any thoughts??? Thanks in advance for any and all assistance.

Grace

[Leith Ross]

Hello Grace310,

If the claim number equals the row number, you don't need offset. You can address the cell directly.

Please Login or Register  to view this content.

[Grace310]

Lieth:
Thank you for your quick response. However, when I changed the code, I get the Runtime 13, Type Mismatch error. I also tried placing Sheet(1) in front of the Range and Cell, but then it gave me the dreaded Runtime 1004, App-defined / Object-defined error. I have been stumped by this tiny problem for over 2 days now.

Grace

[Leith Ross]

Hello Grace310,

Can you post a sample workbook that I can review for you?

[Grace310]

Sure thing. I'll run through it really quickly for you. There are two workbooks: Claim Form (CF) and Claim Log (CL). The CF is the initially filled out by a rep when a claim comes in. The rep fills in some of the fields and selects who they believe the claim should go to next (Current Owner field). If you want to test all the capability, please make sure the Current Owner is always set to Miller, Lance. The macro will first search to see if a form for this customer already exists (we'll assume it does not). If not, it will look for the next empty row in the CL and retrieve the Row number and place it in a field on the CF. It will then paste some of the fields on the CF into their corresponding cells of the next empty row on the CL. Once the pasting is completed, it will save the CL and close it, and it will save the CF to a specified location and close it, giving it the filename of cell B1 followed by the row number that corresponds to that claim on the CL (we'll have multiple claims for the same customer, which is we can't just use the customer name). It will then email that form to the current owner. The original claim form will be cleaned up, saved, and closed.

Once the current owner receives the CF, they will make additions, possibly approving or denying the claim. The macro begins by looking to see if the file already exists. In this case, it does. The corresponding row on the CL needs to be updated with the new information. This is were it gets a little strange. Because of many errors, I decided to paste the row number (which was originally brought over from the CL to be used as part of the filename) back on the CL in cell A2. I'm trying to use this variable in A2 so I can get to the correct row of the corresponding information for specific claim that is being updated. This is were I'm stuck. When fixed, the changes to the CF will be pasted on the correct row. Also, the current owner could change again. Needless to say, the macro will save the CL and close it, save the updated CF and close it, and email the updated CF to the new owner of the claim.

In writing this, I just discovered a new step that will need to be included. Probably an If Then statement depending upon whether a decision has been made whether to approve or deny the claim. But that is another story.

Anyway, here it is and sorry for being long winded.

[Leith Ross]

Hello Grace310,

Thanks for posting those workbooks. I will have to save these and alter the macro so it doesn't send out any emails, etc. This will take a little time to do. I will probably clean the code up as I work on this. Most of the code was recorded and not very efficient.

[Grace310]

Thank you. I should have mentioned earlier that I was not too well versed in VBA. Much of it was recorded, and some is snippets of code that I found (such as the email function). No offense taken, and viewing a cleaner code compared to what I had will only enhance elementary VBA skills.

Lance (Grace's Dad)

[Grace310]

Thanks to all for their help with this. I ended up doing the following and it worked:

Please Login or Register  to view this content.