getting run-time error 91 object variable with block variable not set

**mso3** · 12-20-2015, 04:49 AM

Hi friends,

I want to copy-paste the salary of each employee on the ‘Entry’ sheet having a remark ‘ok’ in column ‘Y’ on the ‘Salary’ sheet. If there is no ‘ok’ remark in column ‘Y’ then I want to skip that employee. Now I have no remark ‘ok’ for second employee. So I want to skip him and want to copy-paste only 3 employees’ salary on salary sheet respectively. Here I don’t want to leave the blank rows for second employee.

I’m getting run-time error 91 object variable with block variable not set.

Any help will be highly appreciated.

Thanking you in anticipation.

**GilEytan** · 12-20-2015, 06:26 AM

hi mso3,

I have looked into your code,
You declare "target" variable, but you never use it, or set a value to it.

Gil

**mso3** · 12-20-2015, 07:50 AM

Hi Gil,
Thank you for your response.

The number of employees may vary time to time.
I want to shorten the code to achieve the target.

Please suggest me a code to achieve the target. What change should I do in the code for require output.

Thanking you,

**GilEytan** · 12-20-2015, 09:46 AM

it should be something like this:

Sub copySalary()
Dim src As Range
Dim dst As Range

Set src = ThisWorkbook.Worksheets("entry").Range("a4:a100")

Set dst = ThisWorkbook.Worksheets("salary").Range("a6")

For i = 1 To src.Rows.Count
If IsNumeric(src.Rows(i, 1)) And src.Rows(i, 25) = "ok" Then
dst(i, 1) = src(i, 1)
dst(i, 2) = src(i, 2)
dst(i, 3) = src(i, 24)
End If
Next

End Sub

**mso3** · 12-20-2015, 12:06 PM

Hi Gil,

Thank you.

I’m getting ‘run-time error 1004 application-defined or object-defined error’ on the following line:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Thanking you,

**GilEytan** · 12-20-2015, 01:18 PM

sorry

here is the fix:

Please Login or Register  to view this content.

**jolivanes** · 12-20-2015, 07:01 PM

See attached

Please Login or Register  to view this content.

**mso3** · 12-20-2015, 07:45 PM

Hi jolivanes,
Greetings from me!

Thank you. The code is working fine but I need the following amendments in it.

1. Each entry contains 3 rows which should be copied on salary sheet. Now only first row of each entry is being copied on the salary sheet.

2. There is a blank row in between each entry which should be there on the salary sheet.

Everything else is fine.

After these 2 amendments the code will be perfect as per my requirement.

Thank you and have a nice time.

**mso3** · 12-20-2015, 07:55 PM

Hi Gil,

Sorry! The code is not working as per requirement. It copies only first 3 cells of each entry and leaving blank rows for skipped entry here entry 2.

Thank you.

**jolivanes** · 12-20-2015, 08:45 PM

Try this.

Please Login or Register  to view this content.

**mso3** · 12-20-2015, 09:50 PM

Hi jolivanes,
Thank you. Sorry, still it's not working. Now it's not copying any entry. The 'ok' remark is in column 'Y' 25. I changed it but no output.

Please see the attached workbook and suggest me a solution.

Thanking you.

**mso3** · 12-20-2015, 09:58 PM

Hi Gil,

I have amended the code as follows and got the entire first row on salary sheet. Now I want the second and third row of each entry on salary sheet and a blank row in between each entry.

Please Login or Register  to view this content.

Thanking you.

**jolivanes** · 12-20-2015, 11:07 PM

Works for me.
The only thing I changed is that I assigned the new code to the button

**mso3** · 12-23-2015, 08:01 AM

Hi jolivanes,

Excellent! I pasted the code in new workbook and now it's working fine.

I appreciate you for the same.

Thank you and have a nice time.

getting run-time error 91 object variable with block variable not set

LinkBack

Thread Tools

Rate This Thread

Display

getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Re: getting run-time error 91 object variable with block variable not set

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Run-time error 91: Object variable or With block variable not set

Run Time Error 91 - Object variable or With block variable not set when using Pie Chart

Run Time Error 91 - Object Variable or With block variable not set

Run-time error 91 - Object variable or with block variable not set

VBA Loop, Find & Copy run-time error '91': Object variable or With block variable not set

Run-time Error '91':Object Variable or With block variable not set When Runing XLA Add-in

[SOLVED] Intermittent Run-time Error 91: Object Variable or With Block variable not set

Bookmarks

Bookmarks

Posting Permissions