<div>
Here is my suggestion. I created a combobox in sheet sugar where a small code fills it from 1 to 12 which are the Months. and modified the VLOOKUP to point to cell F4 that is linked to this combobox. So you can now select a month that you want and see the results instantaneously. If the month has not been active you will get a message Not run Yet. You can change the message in the formula to something that suits you.<br />
<br />
PLease let me know if this is what you need or we can modify it.<br />
gowflow
</div>


vlookup-question-V01.xlsm

<div>
Gowflow....I tried your example but I don't see the VLookup statement being modified. Its still hard coded with a 5.<br />
thx
</div>


<div>
You must be opening the wrong file.<br />
<br />
Look at the file I posted which is:<br />
vlookup-question-V01.xlsm <br />
<br />
Maybe by mistake you opened yours. Mine has a combobox and the formula has $F$4 and not a 5 !!!<br />
check again<br />
gowflow
</div>


<div>
Both solutions worked but using the 2 lines of code is much easier for the user than dealing with a combo box change. Thanks to both for your efforts.
</div>


<div>
Noted your comment however please allow me a comment.<br />
<br />
My solution allow the user to select any month and view the results at ANY time.<br />
thank you.<br />
<br />
gowflow
</div>


<div>
<div class="content wysiwyg-content">
I hope I can explain this clearly. I have an Excel file that shows YTD months and a total that I need to create a macro for to do some formatting, and a VLookup. I know how to do that. Problem is, the column # in the VLookup will change every month as the total column will get pushed over as a new month is added. I attached a mock up of the file, if you go to the SUGAR worksheet you will see the macro button. Running it now works fine because the VLookup is =VLOOKUP(B:B,Sheet1!C:H,5,<wbr />FALSE) where column # 5 is the total column from the pivot table. Next month when April is added the total column # will be 6, and so on. Not sure how to control that in the macro. Hope this makes sense.<br />
thx experts...<br />
BobR<br />
<a href="https://filedb.experts-exchange.com/incoming/2017/04_w16/1157319/vlookup-question.xlsm" target="_blank" class="file-inline" title="samploe file with macro (89 KB)"><svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 512 512"><path d="M67.508 468.467c-58.005-58.013-58.016-151.92 0-209.943l225.011-225.04c44.643-44.645 117.279-44.645 161.92 0 44.743 44.749 44.753 117.186 0 161.944l-189.465 189.49c-31.41 31.413-82.518 31.412-113.926.001-31.479-31.482-31.49-82.453 0-113.944L311.51 110.491c4.687-4.687 12.286-4.687 16.972 0l16.967 16.971c4.685 4.686 4.685 12.283 0 16.969L184.983 304.917c-12.724 12.724-12.73 33.328 0 46.058 12.696 12.697 33.356 12.699 46.054-.001l189.465-189.489c25.987-25.989 25.994-68.06.001-94.056-25.931-25.934-68.119-25.932-94.049 0l-225.01 225.039c-39.249 39.252-39.258 102.795-.001 142.057 39.285 39.29 102.885 39.287 142.162-.028A739446.174 739446.174 0 0 1 439.497 238.49c4.686-4.687 12.282-4.684 16.969.004l16.967 16.971c4.685 4.686 4.689 12.279.004 16.965a755654.128 755654.128 0 0 0-195.881 195.996c-58.034 58.092-152.004 58.093-210.048.041z"/></svg>vlookup-question.xlsm</a>
</div>
</div>


vlookup-question.xlsm

I hope I can explain this clearly. I have an Excel file that shows YTD months and a total that I need to create a macro for to do some formatting, and a VLookup. I know how to do that. Problem is, the column # in the VLookup will change every month as the total column will get pushed over as a new month is added. I attached a mock up of the file, if you go to the SUGAR worksheet you will see the macro button. Running it now works fine because the VLookup is =VLOOKUP(B:B,Sheet1!C:H,5,FALSE) where column # 5 is the total column from the pivot table. Next month when April is added the total column # will be 6, and so on. Not sure how to control that in the macro. Hope this makes sense.
thx experts...
BobR

Excel VLookup in a macro where returned column changes every month

VBScript (Visual Basic Scripting Edition) is an interpreted scripting language developed by Microsoft that is modeled on Visual Basic, but with some important differences. VBScript is commonly used for automating administrative and other tasks in Windows operating systems (by means of the Windows Script Host) and for server-side scripting in ASP web applications. It is also used for client-side scripting in Internet Explorer, specifically in intranet web applications.

VB Script

Golang, also called Go, is an open source programming language that is a statically-typed language with syntax loosely derived from C, adding automatic memory management, type safety, some dynamic-typing capabilities, additional built-in types such as variable-length arrays and key-value maps, and a large standard library. Go is a general-purpose systems programming language that aims to be efficient both for development and execution with a focus on fast compilation and increased maintainability of large projects. Go was originally targeted at systems programming tasks such as building server/web applications, high throughput middleware and databases.

Golang

Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Microsoft Excel

Microsoft Office is an integrated suite of applications that includes Outlook, Word, Excel, Access, PowerPoint, Visio and InfoPath, along with a number of tools to assist in making the individual components work together. Coding within and between the projects is done in Visual Basic for Applications, known as VBA.