Autofit VBA question

[helpme20]

I am trying to write a VBA formula using the Workbook Sheetchange formula and I need some help please.

Private Sub Workbook_Open()

End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Columns().AutoFit
End Sub

I only want it to work with one of my sheets. it is named ProductFinder and I only want it to work for the data in cells, $Q$6:$W$104 (BTW I have named that cell range "Overall" if that helps)



Can someone help me with this? Thank you!

 

[Joe4]

OK, I think I am assuming that "T4" is not on the "ProductFinder" sheet, and Fluff's code is if it is on the same sheet.
So, that should cover both bases. One of those should work for you.

In looking back at it again, I believe Fluff's assessment may be correct. I got thrown off when you mentioned another sheet. But in reading it a few more times, it sounds like you are saying that the T4 is on the "ProductFinder" sheet, and it is just pulling data from another sheet.

 

[helpme20]

It will, whenever you change (manually or by code) a cell in the range Q6:W104

 

[helpme20]

This isn't working the way I have it setup

 

[Joe4]

You put the code in a new module (under "Module1").
You need to put it in the proper sheet module ("ProductFinder").

Double-click on "Sheet4 (ProductFinder)" in the VB Explorer and put the code int THAT window.
Automated VBA code only runs when placed in a "Microsoft Excel Object" (and put in the proper Event). Things put under "Modules" will not run automatically.

 

[helpme20]

You put the code in a new module (under "Module1").
You need to put it in the proper sheet module ("ProductFinder").

Double-click on "Sheet4 (ProductFinder)" in the VB Explorer and put the code int THAT window.
Automated VBA code only runs when placed in a "Microsoft Excel Object" (and put in the proper Event). Things put under "Modules" will not run automatically.

I really really appreciate your help.

I'm sorry if I appear to be a little slow, but I think I have it pasted properly, but it still does not autofit for that range of cells

 

[Joe4]

How exactly are you updating the value in cell T4 of your "ProductFinder" sheet?
Is T4 part of a merged cell?

How exactly do the values in "Q6:W104" get updated?
Are they formulas? If so, please post what the formulas look like.

 

[Fluff]

How is T4 on that sheet being changed?

 

[helpme20]

How exactly are you updating the value in cell T4 of your "ProductFinder" sheet?
Is T4 part of a merged cell?

How exactly do the values in "Q6:W104" get updated?
Are they formulas? If so, please post what the formulas look like.

Cell t4 is not a merged cell

in sheet3 named GuideData there are

There is a data in Columns A thru G and data in Columns 2 thru 99

in Sheet4 the Product Finder sheet, when you enter anything in cell T4, it returns the information from Sheet3 to columns Q thru W. each row has formulas in cells Q6 thru W104

q6 =IFERROR(VLOOKUP(ROWS($Q$6:Q6),$H$6:$O$104,2,0),"")

r6 =IFERROR(VLOOKUP(ROWS($Q$6:U6),$H$6:$O$104,8,0),"")

s6 =IFERROR(VLOOKUP(ROWS($Q$6:Q6),$H$6:$O$104,3,0),"")

t6 =IFERROR(VLOOKUP(ROWS($Q$6:S6),$H$6:$O$104,4,0),"")

u6 =IFERROR(VLOOKUP(ROWS($Q$6:T6),$H$6:$O$104,5,0),"")

v6 =IFERROR(VLOOKUP(ROWS($Q$6:U6),$H$6:$O$104,7,0),"")

w6 =IFERROR(VLOOKUP(ROWS($Q$6:V6),$H$6:$O$104,6,0),"")

 

[Fluff]

You need to save the workbook as macro enabled, otherwise the code will be deleted.
Is T4 being changed manually?

 

[helpme20]

You need to save the workbook as macro enabled, otherwise the code will be deleted.
Is T4 being changed manually?

yes manually