Help understanding why VBA macro is not working

sts8500man

Board Regular
Joined
Jul 12, 2012
Messages
77
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
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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
 
Upvote 0
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
 
Upvote 0
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")
 
Upvote 0
Unfortunately, same problem -- stopped on the same line of code and got the same error message. Any other ideas?
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

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
Back
Top