Forum Discussion
Solved
VBA to trap error for Column entry
The SUB below can capture the errors for non-letters and more than 3 characters for Column entry but can not detect if beyond XFD is entered. like XXX - it just looped! i.e. legit entry should be A ...
The problem goes to the variable "mcol" when it is beyond XFD. You include a checking before the for-loop as below:
check_col = Cells(1,mcol).column
If check_col > 0 then
' it is good and okay to run the next steps
else
msgbox("Input does not refer to a column. ")
exit sub
end ifI am not sure if there is other easier way to do. Hope that it can help you.
Mr. Chan I searched the net re:below but I can not find any article about it. Is it a VBA function? many thanks check_col = Cells(1,mcol).column
Mr. Chan I searched the net re:below but I can not find any article about it. Is it a VBA function? many thanks check_col = Cells(1,mcol).column
Cells(i,j).Column will return the column number for the cell.
So, Cells(1,"AA").column will return 27.
I tested that Cells(1,"XFD").column returns 16384 while Cells(1,"XFE").column returns nothing/null. So I consider the criteria ">0" for the checking of the existence of the column.
- Mr. Chan sorry if the message will be garbled. We are experiencing bad internet connection here.. YOU HAVE MENSA-LIKE DEDUCTIVE PROWESS! few can conjure something out of almost nothing. I have searched (maybe I didn't search deep enough) the net about this topic and I couldn't find an article close to this. with your system, many others will benefit from this. more power and good health..