Help understanding why VBA macro is not working

sts8500man

Board Regular
Joined
Jul 12, 2012
Messages
74
Office Version
  1. 365
Platform
  1. Windows
I am an occasional non-programmer user of Excel. I have a VBA macro that does not work properly and I cannot figure out why. Below I provide the portion of the macro that is giving problems. I would appreciate someone providing a perspective of what is wrong with it and suggesting a solution.

The programming for the “If InStr… function” works just fine. The part in the “Else” function works correctly where the “wbs.sheets” workbook is concerned but the macro stops working when it gets to the line that start with “wbR.Sheets…” for pasting into cell B10. It seems like it is exactly like the one in the "If InStr..." function above it that works perfectly. I don’t understand why it works in the “If InStr… function” but it does not work in the “Else” function??

I trust I am following the new guidelines properly. I do not understand all the technical jarcon in the guideline rules.

Dim wbS As Workbook: Set wbS = Workbooks("Downloaded.xls")
Dim wbR As Workbook: Set wbR = Workbooks("manipulate download.xlsm")
With wbS.Sheets(1)
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
Const TxtToFind = "market"
Dim wbS As Workbook: Set wbS = Workbooks("Downloaded.xls")
Dim wbR As Workbook: Set wbR = Workbooks("manipulate download.xlsm")
With wbS.Sheets(1)
If InStr(1, .Range("A7").Value, TxtToFind) > 0 Then
wbS.Sheets(1).Range("F7").Copy
wbR.Sheets(1).Range("B10").Select
ActiveSheet.Paste
Else
With wbS.Sheets(1).Range("A7").EntireRow.Insert
wbS.Sheets(1).Range("A7").FormulaR1C1 = "downloaded"
wbS.Sheets(1).Range("F7").FormulaR1C1 = "0"
wbS.Sheets(1).Range("F7").Copy
wbR.Sheets(1).Range("B10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End With
End If
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,258
Office Version
  1. 365
Platform
  1. Windows
You cannot select a cell in a non-active sheet, try
VBA Code:
wbS.Sheets(1).Range("F7").Copy wbR.Sheets(1).Range("B10")
Application.CutCopyMode = False
End With
 

sts8500man

Board Regular
Joined
Jul 12, 2012
Messages
74
Office Version
  1. 365
Platform
  1. Windows
Fluff, I appreciate your reply, unfortunately your proposed solution did not work. I incorporated it into the macro by copying and pasting from your input and tried running the macro again. The macro stopped at the same place where it always stopped - on the line --
wbS.Sheets(1).Range("F7").Copy wbR.Sheets(1).Range("B10")
and provided the error message below which is the same error message I was getting previously. Any other ideas?
1590180535858.png
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
53,258
Office Version
  1. 365
Platform
  1. Windows
You need to remove this part of your code
VBA Code:
wbS.Sheets(1).Range("F7").Copy
         wbR.Sheets(1).Range("B10").Select
         ActiveSheet.Paste
And replace it with
VBA Code:
wbS.Sheets(1).Range("F7").Copy wbR.Sheets(1).Range("B10")
 

sts8500man

Board Regular
Joined
Jul 12, 2012
Messages
74
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Unfortunately, same problem -- stopped on the same line of code and got the same error message. Any other ideas?
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
You are not properly using the With statement.
With wbS.Sheets(1)
If InStr(1, .Range("A7").Value, TxtToFind) > 0 Then
wbS.Sheets(1).Range("F7").Copy


You have duplicate declarations of these variables:
Dim wbS As Workbook: Set wbS = Workbooks("Downloaded.xls")
Dim wbR As Workbook: Set wbR = Workbooks("manipulate download.xlsm")
...
Dim wbS As Workbook: Set wbS = Workbooks("Downloaded.xls")
Dim wbR As Workbook: Set wbR = Workbooks("manipulate download.xlsm")
_____________________________________________________________________________________

Try the following code, it works no matter which book is active.

VBA Code:
Sub test()
  Dim wbS As Workbook
  Dim wbR As Workbook
 
  Set wbS = Workbooks("Downloaded.xls")
  Set wbR = Workbooks("manipulate download.xlsm")
 
  With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
    .EnableEvents = False
  End With
  Const TxtToFind = "market"
 
  With wbS.Sheets(1)
    If InStr(1, .Range("A7").Value, TxtToFind) > 0 Then
      .Range("F7").Copy wbR.Sheets(1).Range("B10")
    Else
      .Range("A7").EntireRow.Insert
      .Range("A7").FormulaR1C1 = "downloaded"
      .Range("F7").FormulaR1C1 = "0"
      wbR.Sheets(1).Range("B10") = "0"
    End If
  End With
End Sub
 

sts8500man

Board Regular
Joined
Jul 12, 2012
Messages
74
Office Version
  1. 365
Platform
  1. Windows
Dante,

Thank you a great deal for helping me with this issue. I still do not understand but since I have no educational background in VBA and very little in excel, I guess I cannot expect myself to understand. Anyway I greatly appreciate your attention to this need and your assistance.
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Glad we could help & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,123,229
Messages
5,600,420
Members
414,383
Latest member
kevinlarey

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top