0

I get Error 1004 "Application-defined or Object-defined error" on this line: 

 `ws2.range(dstRef).offset(srn,0).value= srn+1`

Why is that?

Dim ws1, ws2 As Worksheet
Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Packinglist_Annexure-1")

Dim srcRef, dstRef, tempAdr As Range
Set dstRef = Range("C19")

Dim k, srn As Integer
k = reqRow
srn = 0
For k = reqRow To row1
    ws2.Activate
    ws2.Range(dstRef.Address).Offset(srn, 0).Value = srn + 1
    ws1.Activate
    ws1.Range(reqAddr.Address).Offset(0, srn).Copy Destination:=ws2.Range(dstRef.Address).Offset(1, srn)
    srn = srn + 1
Next k
Improve this question
5
  • error 1004 is thrown At line ws2.range(dstRef).offset(srn,0).value= srn+1 Commented Mar 31, 2018 at 10:00
  • 1
    You'd be best editing and expanding your question rather than adding comments beneath where they may not be noticed. Commented Mar 31, 2018 at 10:27
  • The code puts serial number in one column and data copied into another Commented Mar 31, 2018 at 11:34
  • Your original question was valid and was answered, you've now altered the code for that original question - do you have a further question here? If so please make the original question clear and either add the additional question as subsequent text or as a new question. Thanks Commented Mar 31, 2018 at 12:18
  • @Spd, hey I've got the answer to your 2018-03-31 11:29:12Z state of the art question. Now, what's your next revised question? Commented Mar 31, 2018 at 15:51

3 Answers 3

Reset to default
1

I believe you are looking for the range.address property although I'm unclear on why Range("C19") has no parent worksheet reference.

Dim ws1 As Worksheet, ws2 As Worksheet
Set ws1 = Worksheets("Tabelle1")
Set ws2 = Worksheets("Packinglist_Annexure-1")

Dim srcRef As Range, dstRef As Range, tempAdr As Range
Set dstRef = ws1.Range("C19")

Dim srn As Integer
srn = 0
ws2.Activate
ws2.Range(dstRef.ADDRESS).Offset(srn, 0).Value = srn + 1

Is the . in .Range necessary when defined by .Cells?

You need to declare all of the vartypes in a dim line.

dim a, b, c, d as string

The above only dimms d as a string; everything else is a variant.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Thanks address function worked but copy function is not working
If by copy you mean the value assignment (e.g. ...Value = srn + 1) then it is working for me.
text contents of one list initiated by ' apostrophe need to be transferred.Individual cell gets copied, but in loop it fails
Well, since I cannot see any loop in the code you provided I'll reserve judgement on that part of the question (which in any event has been added after the fact).
1

you either use

ws2.Range(dstRef.Address).Offset(srn, 0).Value = srn + 1

or 

ws2.Range("C19").Offset(srn, 0).Value = srn + 1

BTW you'd better explicily declare all your variables or they will be implicitly assume as of Variant type:

Dim ws1 , ws2 As Worksheet ' w1 is of Variant type and w2 is of Worksheet type

Dim ws1 As Worksheet, ws2 As Worksheet ' both w1 and w2 are of Worksheet type
Improve this answer

Comments

0

The error is occurring as you are trying to set the string (that specifies the cells to be used) as a range rather than a string. You can declare dstRef as a string and use this on that line e.g. 

Dim srcRef, tempAdr As Range
Dim dstRef As String
Dim srn As Integer

dstRef = "C19"
srn = 0
ws2.Activate

ws2.Range(dstRef).Offset(srn, 0).Value = srn + 1
Improve this answer

4 Comments

you are the third answering the same. from how it's going on here, since you are the last one then you get more chances to be marked as the accepted answer...
@DisplayName - hadn't seen the other answers, they didn't seem to show up when I came here from my review queue. Accepted answer has already been selected by the look of it anyhow though.
"Accepted answer has already been selected by the look of it anyhow though", yes and it was the 2nd one
@spd - have you edited the code in the questions to a version that now works? If so you shouldn't do that as should someone come up with a similar issue they may not understand the problem you have from your question.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.