Strange Problem

Paddy1979

Well-known Member
Joined
Sep 23, 2005
Messages
608
Hello all, please can advise me on this problem.

My spreadsheet is designed to gather batches of information onto a summary sheet.

On my spreadsheet I have a userform with 2 options that allows the user to either
A. refresh productivity weekly figures
&
B. refresh period end figures

If A (productivity) is run before B (period end) , B will not work.

However if i close the spreadsheet and reopen and retry option B it then runs perfectly , so its seems A is impacting on B somehow.

A and B are both written modules shown below, can you see why B bugs out on the below code line only if A is run first?

Code:
S1 = .Find(What:=PR, After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Column - 1

Many thanks
Paddy

A.Productivity code
Code:
Sub Productivity() 

Dim PROD As Worksheet 
Dim rfile As Variant 
Dim dnumber As Variant 
Dim X As Long 

Set PROD = Sheet4 

Sheet4.Unprotect Password:="Pass" 
X = Sheet4.Range("C65334").End(xlUp).Row + 1 
    
    MsgBox "Please open the weekly Productivity INNS035 report" 
    rfile = Application.GetOpenFilename("Microsoft Excel Workbooks,*.xls") 
    If rfile = False Then Exit Sub 
    Workbooks.Open Filename:=rfile 
    Sheets("Technician Detail").Select 
    Set Data1 = ActiveWorkbook 
      
here1: 
    dnumber = Application.InputBox("Please input the Week number the INNS035 data reflects", "Week Number", "") 
    If dnumber = False Then Exit Sub 
    
    If Left(dnumber, 1) <> "W" Then 
    MsgBox " With Prefix 'W'" 
    GoTo here1 
    End If 
    Set c = PROD.Columns(3).Find(dnumber, LookAt:=xlWhole, MatchCase:=False) 
    
    If Not c Is Nothing Then 
    MsgBox "Aborted ! Data is already present for this Week", vbCritical 
    Exit Sub 
    Else 
    End If 
    
    With Columns("B") 
    R = .Find(What:="Steven Lee", After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Row 
    End With 
        
    Range("B" & R & ":AS" & Cells(Rows.Count, "B").End(xlUp).Row).Copy 
    
    With PROD 
    .Activate 
    .Range("D" & X).PasteSpecial xlPasteAll 
    End With 
    
    With PROD.Range("A" & X & ":AS" & Cells(Rows.Count, "D").End(xlUp).Row) 
    .Columns(3).Value = dnumber 
    .Columns(1).FormulaR1C1 = "=VLOOKUP(RC[2],wk_p,3,0)" 
    .Columns(2).FormulaR1C1 = "=VLOOKUP(RC[1],wk_p,2,0)" 
    .Columns(45).FormulaR1C1 = "=SUM(RC[-11]:RC[-9],RC[-7]:RC[-5])+SUM(RC[-8],RC[-3],RC[-2])/2" 
    .Copy 
    .PasteSpecial xlPasteValues 
    End With 
    
    Data1.Activate 
    ActiveWorkbook.Close SaveChanges:=False 
    
    Sheet4.Protect Password:="Pass" 
    
    Sheet3.Select 
    
 End Sub


B. Period Reports, (not all code , just to debug point)

Code:
Sub periodend() 

Dim PR As Variant 
Dim lr As Long, i As Long 
Dim S1 As Integer, p1 As Integer, c1 As Integer, n1 As Integer 
Dim cell As Range 
Dim home As Workbook 
Set home = ActiveWorkbook 

Application.Calculation = xlCalculationManual 

here: 
PR = Application.InputBox("Please input the Period number the data reflects (with Capital P)", "Period Number", "") 
If PR = False Then Exit Sub 
   If Left(PR, 1) <> "P" Then 
   MsgBox " With Prefix 'P'" 
   GoTo here 
End If 

'set start points for data entry   '' 
With Sheet5.Rows("1:1") 
S1 = .Find(What:=PR, After:=.Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=True).Column - 1 
End With
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
In A you end with Sheet3 selected then in B you try to start working on Sheet5 without making it the active sheet first Find/Search are real sticky about how you define the range they work on try adding: "Sheets("Sheet5").Select" before the find part of your code and re-test?
Like this:

Sheets("Sheet5").Select
With Sheet5.Rows("1:1")

This type of code should be in a Standard code module, like: Module1 and not a Sheet module like you have it!

It is a bad practice to just use the sheet name even in a Sheet module!

Code your Sheets, like this:

Sheets("Sheet1")

Not: like, Sheet1.
 
Upvote 0
Hi Joe thanks for the tips, but i'm still having the same problem.

I can get around it by instructing users to run B before A. But inevitably someone will forget.

Cheers
Paddy
 
Upvote 0
In looking at your code it looks like "A" is opening a workbook and that workbook may be the one that "B" will work with after "A" has run, rather than the courrent application?

Try closing the Data Workbook after you get done and select the Default Workbook within "A" this way "B" will start fresh?
 
Upvote 0
Hi Joe, good idea but the workbook A opens is not utilised at all in the B module.

Again thanks for the response.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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