on 12-20-2013 9:55 AM
Hi All,
I am currently try to work on a blog where its aim to input the values from excel and based on the values it has to get the values from DB in sap and display it in the Output portion of the excel as mentioned in the following blog http://scn.sap.com/docs/DOC-31015. I have created the FM and done the basic things mentioned in the blog but I am facing an error "Run Tme Error '40036' Application-defined or object-defined error". Please find my code below and help to resolve the issue.
With Regards,
Giriesh M
Hi there...
It is Simply not possible for Excel to Parse that Code because There is no Existing for
ThisWorkbook.ActiveSheet.Cells...
Try it via AutoCompletion (or press "F1" in Context help)
If you set the Worksheet in a Variable like this:
[code]
dim sht as Worksheet
Set sht = "Tabelle1"
[/code]
you can use it like that:;
[code]
If sht.Cells(6,2).Value <> " "
[/code]
greetings
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi Thomas,
I have changed the code as below:
[Code]
Dim sht as Worsheet
sht = ThisWorkbook.Activesheet
If sht.cells(6,2).Value <> " " then
End If
[/CODE]
This code works for me as expected.
Instead of assigning the ThisWorkbook.Activesheet to sht if I enter it directly as
If ThisWorkbook.Activesheet.cells(6,2).Value <> " " then
End If
It is throwing the above error. What is the reason behind assigning it to the variable and implementing it directly.
With Regards,
Giriesh M
Hi Griesh,
Its not assigning to a Variable - its making an Instance you are Assigning to an Object-
So in this Case you make an Instance of the Sheet-
The Biggest difference is you can Handle it easier (as you can see)
and if there are more actions in the Sheet to perform you can also do like this:
Dim sht as Worksheet
Set sht = ThisWorkbook.ActiveSheet
With sht
if .Cells(6,2).Value <> "" Then
...' Do Something
else
end if
End with
From Where are you running your code ?
From the VBA Editor per "F5" ?
This cant run---because the VBA Editor has no "ActiveSheet" from where it can Start
Add a Button to your Sheet and assign it to the Makro "GetAdress"
Press the button and than ActiveSheet will run also without Setting to an Instance.
Have a nice day (and a happy new year !)
Thomas Bräutigam
Hello.
As far as I see you have your code in Sheet1.
I suggest to move this code into Module1. Then I guess you will not get any runtime error.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Giriesh,
it seems you should change Cells(6, "B") for Cells(6, 2) on the line where the error is.
Try changing this.
Cheers,
Michael
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hello Giriesh, I tried to reproduce your error on that line and I couldn't. I misspelt ActiveSheet to see if I could force the error. What I got was the misspelt word hightlighted in blue, indicating there's something wrong there. On your printscreen there's no blue highlight. The red highlight you show is generated by adding an interruption point/breakpoint. Can you add one about 15 lines before the one you have now, run the sub/funtion and using F8 key advance line by line until you get to the line with the problem?
Michael
Hi Michael,
As you mentioned the blue highlight is the compile error. But In my case I spelt it correct. Tried to debug it but getting the error only at the line "If ThisWorkbook.ActiveSheet.Cells(6, 2).Value <> " " Then".. Unable to proceed further. Please find the below image:
With Regards,
Giriesh M
Dear Giriesh,
This is a long shot but try putting the following before the problematic line:
ThisWorkbook.Sheet1.Activate
Or one by one i.e.
ThisWorkbook.Activate
Sheet1.Activate
This way we can see where exactly the error come from the Excel object hierarchy.
You can also try assigning the cell to a range object i.e:
DIm rng As Range
Set rng = ThisWorkbook.ActiveSheet.Cells(6,2)
If rng.Value <> "" Then etc.
Let see what happen..
Thanks
Sayuti
p/s: Your username and password are visible in one of your screenshots. You may want to consider masking it next time..
Dear Giriesh,
Is there any variables defined with reserve names e.g. Dim Application As Object or Dim Worksheet As Object.or something like that in your code?
Perhaps you can change ActiveSheet to the specific sheet e.g. Sheet1 and see what happens.
When I googled the error msg and number, most of the results suggest that there is a glitch in Excel itself or a corrupted database and recommended a re-installs.
Have you test your codes in a different PC?
Thanks,
Sayuti
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.