sts8500man
Board Regular
- Joined
- Jul 12, 2012
- Messages
- 77
- Office Version
- 365
- Platform
- 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
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