

October 24, 2017

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 24, 2017

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

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

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


VIP

Trusted Members

June 25, 2016


VIP

Trusted Members

June 25, 2016

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
1 Guest(s)
