Log In
Register
Home
October 24, 2017
Offline
Hi All
Hope you all are doing well !
I need help to resolve the problem which i am facing.
I used VBA userform to input data into a table name "Inv_Data" and sheet name is "Data" and use following code to find last empty row to input new record into table "Inv_Data".
Source!F2=COUNTA(Inv_Data[ID]) .........."ID is first column of table and giving number to new records"
erow = Sheets("Source").Range("F2").Value + 1
and i am getting "run time error 2147417848 Method 'value' of object 'range' failed", after closing and reopening the file error does not appear. i don't know the reason.
please guide me how to get rid of this error.
October 5, 2010
Offline
Hi Irfan,
Please supply the workbook with code+form so it is easier to help you.
Regards
Phil
October 24, 2017
Offline
Thanks Philip Treacy for your response.
I have attached the file, the userform which is giving error is linked with tab "Diray Punching" and i used following code to find last empty row to input new record into table "Inv_Data".
Source!F2=COUNTA(Inv_Data[ID]) .........."ID is first column of table and giving number to new records"
erow = Sheets("Source").Range("F2").Value + 1
VIP
Trusted Members
June 25, 2016
Offline
Hi Irfan
I was unable to reproduce the error in Excel 2010 but it occurred in Excel 2013.
No idea why a different version should trigger this error (it crashed Excel 2013).
Tried saving your file as XLSM but it still crashed.
The code that triggered the error started from this onwards.
Sheets("Data").Range("A1").Offset(erow, 0).Value = erow
Tried changing the codes to CELLS() also did not help.
Using On Error Resume Next also caused it to crash.
Sorry, can't help you at this moment as I don't have Excel 2013 (I borrowed a copy from my colleague for earlier testing).
Sunny
October 24, 2017
Offline
Thanks SunnyKow !
i am using Excel 2016 and file is also crashing excel 2016.
i also tried to change my erow with following code and still same problem
erow = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
is there is any other way of writing code to get data from user form into excel?
VIP
Trusted Members
June 25, 2016
Offline
Hi Irfan
Try this. Hope it helps.
Sunny
October 24, 2017
Offline
Thanks SunnyKow for your time and efforts but still facing the same problem. i also tried following code but still the same error.
Sheet3.Cells(erow, 2) = Me.Txt_PO
and for erow following code
erow = Worksheets("Data").Range("A1").CurrentRegion.Rows.Count
VIP
Trusted Members
June 25, 2016
Offline
OK. I finally got a copy of Excel 2013 to test.
Hope this time it works as I have tested it many times and it looks OK.
Sunny
October 24, 2017
Offline
I am using Excel 2016, and still same error. I think code should work for all version of MS Excel.
July 15, 2018
Offline
Hi Irfan
Try this. Hope it helps.
Vasim
erow = Sheets("Data").Range("F" & Rows.Count).End(xlUp).Row + 1
VIP
Trusted Members
June 25, 2016
Offline
Hi Irfan
The last file I attached Finance-Diary-Inprocess3.xlsb is working fine in my Excel 2013 (32-bit).
I don't know what else I can do to resolve your problem.
If the original file is created from an older Excel version, then maybe you can try recreating the whole thing from scratch in Excel 2016.
This problem seems to be very common from what I googled and there doesn't seem to be a solution.
Even simple VBA codes that refer to range is failing. This is one reason why I am reluctant to upgrade my Excel 2010 to a newer version.
I faced numerous 32/64-bit compatibility issue, library references issues etc, all relating to using Userform. I ended up spending days converting codes to Late Binding to cater for the 64-bit issues. Codes that have been running smoothly for years can suddenly fail.
Very frustrating for a non-professional/expert programmer like me.
Hopefully some experts here can help you on your issue.
Sunny
VIP
Trusted Members
June 25, 2016
Offline
Hi Irfan
I tried converting your Data table to a normal range and it seems to work. Even your original file is working.
You will need to change the Source sheet formulas in cells F1 and F2 to refer to more rows once you convert.
See if this helps.
Sunny
1 Guest(s)
