Excel VBA Forgetting Variable Value After Loop

Question

I'm working on a macro that will combine 2 ticket lists together. I have a problem with a loop I am using to find the column number of needed columns. One variable (KJtknrCol) is properly defined while the loop is running (confirmed with message box prompt) but as soon as the script is out of the loop, the value for my variable is missing (also confirmed by message box prompt). Any help would be appreciated.

Here is my code:

Dim KJtknrCol As Long

KJtknrCol = 0

KKtktnrCol = 0

KLstActvCol = 0

KRatngCol = 0

KstatCol = 0

tmpval1 = 1

Do Until Sheets("TW-KPM Export").Cells(1, tmpval1).Value = ""

If Sheets("TW-KPM Export").Cells(1, tmpval1).Value = "Active User" Then KassigneeCol = tmpval1

If Sheets("TW-KPM Export").Cells(1, tmpval1).Value = "Jira-Nr." Then

KJtktnrCol = tmpval1

MsgBox "KJtktnrCol has been found in column " & KJtktnrCol & Chr(13) & "tmpval1 = " & tmpval1

End If

If Sheets("TW-KPM Export").Cells(1, tmpval1).Value = "KPM-Nr." Then KKtktnrCol = tmpval1

If Sheets("TW-KPM Export").Cells(1, tmpval1).Value = "Last Activity" Then KLstActvCol = tmpval1

If Sheets("TW-KPM Export").Cells(1, tmpval1).Value = "Rating" Then KRatngCol = tmpval1

If Sheets("TW-KPM Export").Cells(1, tmpval1).Value = "Ticket Status" Then KstatCol = tmpval1

tmpval1 = tmpval1 + 1

Loop

MsgBox "KJtknrCol (After loop) = " & KJtknrCol

During the loop, KJtknrCol is found in column 5 (correct)

But after the loop, it's as if Excel resets the value

All other variables are working OK.

Answer 1

Hi. Do a search on your variable: "KJtknrCol" If I am not mistaken, from what you posted, it is only assigned 0. It is never assigned another value elsewhere in your posted code.

Answer 2

Dear @Oleg Skotsen, 

Thank you for posting your question in the Microsoft Q&A forum.  

I’ve been looking closely at your requirements, while this forum is excellent for general app usage, Stack Overflow and Tech Community are the definitive gold standard for coding challenges like this. 

To ensure you receive the highest level of technical expertise, I strongly recommend sharing this query on the Newest Questions - Stack Overflow and Contributing to the Community | Microsoft Community Hub. The communities there consists of developers who specialize in VBA and can offer optimized code snippets that are often beyond the scope of general application support. Posting there will put your code in front of thousands of experts who can help you refine the logic or identify syntax conflicts that might be hidden. 

I greatly appreciate your understanding. I hope you quickly find a solution for your issue. 

Looking forward to your response! 

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.