I've encountered an issue and I've been unable to find any literature on how to solve it. I'm importing about 30 source files into a single workbook. Of these, roughly 20 of them come from other areas of my company. I have zero control over them altering the layout of these source files, so I'm running into an issue where the code is either failing OR I'm getting incorrect results as a result of the layout change. Example: My code copies the date in column DN of the source file and pastes it into the destination workbook. Now that date is in column DO, so I'm getting incorrect results. Is there an efficient way to review the header text to ensure it equals "Date" and if so, proceed with the code. If the header text doesn't equal "Date"; find the header text that does equal "Date", and allow the code to continue with the new column position without manual intervention?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
Error Handling for Layout Changes
- Thread starter reberryjr
- Start date
If cell E1 = Loan Number, but it's on 2 separate lines like this, the code is passing by it. If I put both words on the same line, the code picks it up.
Upvote
0
Excel Facts
How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.
I think I figured it out. Adding this snippet towards the top of my code appears to resovle the issue.
VBA Code:
For Each c In sD.Range("A1:CX1")
c = Replace(c, Chr(10), "")
c = Replace(c, Chr(13), "")
Next c
Upvote
0
I was thinking along the same lines. Keep in mind that if the user puts a space after the first word and then presses ALT + RETURN, you will have the same problem.
