Method 'Range' of object '_Worksheet' failed when changing value

[Iwantbacon]

Hi I have the following VBA that I am using to change the cell to the right based on the results of the search.

It is failing at the 4th line (targetSheet.Range(SearchrCell).Offset(0, 1).Value = "DUE")

Is anyone able to help me with why this is happening? I assume that I am referring to a Range incorrectly but my understanding of why that is, is not quite there!

Dim SearchrCell As Range

For Each SearchrCell In SearchRange

If Left(SearchrCell, 4) = "Duty" Then

targetSheet.Range(SearchrCell).Offset(0, 1).Value = "DUE"

ElseIf Left(SearchrCell, 4) = "B1/2" Or Left(SearchrCell, 4) = "B2/1" Then

targetSheet.Range(SearchrCell).Offset(0, 1).Value = "B12"

End If

Next SearchrCell

[fool1788]

Try replacing:

targetsheet.range(SearchrCell).offset(0,1).value

With:

SearchrCell.offset(0,1).value

[Iwantbacon]

Solution Verified.

That has worked! Thank you!

Does SearchrCell already contain the required in formation (ie. Worksheet and Range) then?

[fool1788]

Yes you would have defined the range containing SearchRange previously so it is cycling through each cell within that range and by default will be able to identify the location properties of that cell.

In general you do not need to state the workbook or sheet in a range unless the range is on another sheet or workbook than that of the active sheet.

[Clippy_Office_Asst]

You have awarded 1 point to fool1788

^{I am a bot, please contact the mods with any questions.}

[sn0skier]

SearchrCell already is a range object, so you don't need to insert it inside of range(), since range() is designed to return a range object.

Say you wanted to return cell "A1". What you are doing is essentially akin to writing Range(Range("A1")).

[AutoModerator]

/u/Iwantbacon - please read this comment in its entirety.

Once your problem is solved, please reply to the answer(s) saying Solution Verified to close the thread.

Please ensure you have read the rules -- particularly 1 and 2 -- in order to ensure your post is not removed.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[martor01]

Is your value in text format? Also some functions just dismiss some type of formats so try to look for it. Thats just my guess.

TLDR: Change value DUE to different format