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.
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 if
I 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..