Link to home
Start Free TrialLog in
Avatar of M Zahid
M ZahidFlag for United Arab Emirates

asked on

how to put end loop at the end of code / formula

I had this question after viewing VB loop to open the file from the network drive.

Having small hitch on the below:-
Worksheets("Top Value").Range("B2:D2").FormulaArray = "=" & fullPath & "Top Values'!C10:E10"
            Worksheets("Top Value").Range("E2:J33").FormulaArray = "=" & fullPath & "Top Values'!G10:L41"
            Worksheets("Top Value").Range("B3").Formula = _
                "=IF(" & fullPath & "Top Values'!C11=0,B2," & fullPath & "Top Values'!C11)"
            Worksheets("Top Value").Range("B3").Copy
            Worksheets("Top Value").Range("B3:D33").PasteSpecial xlPasteFormulas

E.g. as we have asking code to =IF(" & fullPath & "Top Values'!C11=0,B2, then copy the formula to next row& go upto B3:D33
But can we put loop or stop =IF(" & fullPath & "Top Values'!C11= blank or empty, B2, then stop & no copy further??


Thx
MZ
Avatar of Shums Faruk
Shums Faruk
Flag of India image
You can edit your code from 
"=IF(" & fullPath & "Top Values'!C11=0,B2," & fullPath & "Top Values'!C11)"

Open in new window

to 
"=IF(or(" & fullPath & "Top Values'!C11=""""," & fullPath & "Top Values'!C11=""0"")),B2," & fullPath & "Top Values'!C11)"

Open in new window

Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

Hi Shums

Thanks for the Response, Getting error after applying this "  Application-defined or object-defined error "
Avatar of Shums Faruk
Shums Faruk
Flag of India image
Zahid,

Better to upload workbook. I cannot test.
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

i m sorry i cant upload the workbook.
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

Yes Brad, this what i m looking for let me try this & will get back to you.
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

Hi Brad,

got Error on  For Each cel In Worksheets " For Each control variable must be Variant or Object"

& even Sub BuildHyperlinksAndGetValues() got yellow highlighted.

plz advice

User generated image
User generated image
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

Hi Brad,

just got compile error End Select without Select Case?

User generated image

plz advice
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

sorry my mistake while typing the code i forgot add Next statement

but now giving error on statement 13

User generated image
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

Hi Brad,

Sorry coming back late, was bit hands full with some work.

Thanks for the tips too, now the STOP this is working but statement 14 to 15 in the snippet above
{ If cel.Value = "STOP" Then
   Range(cel, Worksheets("Top Value").Range("D33")).ClearContents }
its says it Cell value is contain "STOP" then clear the contents, as per my understanding it means clear wherever is STOP & make it Blank but instead of clearing or its repeating the STOP to blanks cells below.
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

Hi Brad,

As requested here is complete SUB,

{ Sub BuildHyperlinksAndGetValues()
Dim flName As String, flPath As String, frmla As String, frmlaE As String, frmlaF As String, frmlaG As String, fullPath As String, _
    subFolder As String
Dim i As Long, iFirst As Long, iLast As Long
Dim cel As Range

Application.ScreenUpdating = False  'Makes macro run faster
Application.Calculation = xlCalculationManual   'Makes macro run faster
Application.DisplayAlerts = False
'On Error Resume Next

With Worksheets("Master")
    flPath = "\\share\Group\DEPT\GPO Bids\1. C411 Price\"
    subFolder = .[D$6] & "\" & .[$D$3] & " - " & .[$D$4] & "\" & "REV " & .[$D$5] & "\"     'no space before last \
    iFirst = 9
    iLast = 11

    For i = iFirst To iLast
        frmlaE = .[$D$3] & " - C411 - " & .[$D$4] & " " & .Cells(i, "D").Value & " Rev " & .[$D$5] & ".xlsb"
        fullPath = "'" & flPath & subFolder & "[" & frmlaE & "]"
        frmlaF = "=IFERROR(" & fullPath & "Report 1'!$G$48,0)"
        frmlaG = "=HYPERLINK(""" & flPath & subFolder & """&E" & i & ",""Link"")"     'Use when fully debugged
       
            'Use once fully debugged
        .Range("E" & i).Value = frmlaE
        .Range("F" & i).Formula = frmlaF
        .Range("G" & i).Formula = frmlaG
       
            'Put IFERROR formulas to retrieve data on worksheets: _
    Category BD, Top Value, SteelSummary, Discipline, Client Approved Cat, NUMBER OF RFQ MTOs, Offers Validity Status & Currency BD
       
        Select Case i
       Case 9 'Bulk
             Worksheets("Category BD").Range("Q5:S20").FormulaArray = "=" & fullPath & "Report 1'!D81:F96"
           
             Worksheets("Top Value").Range("B141:D141").FormulaArray = "=" & fullPath & "Top Values'!C10:E10"
             Worksheets("Top Value").Range("E141:J231").FormulaArray = "=" & fullPath & "Top Values'!G10:L100"
             Worksheets("Top Value").Range("B142").Formula = _
                 "=IF(" & fullPath & "Top Values'!C11=0,B141," & fullPath & "Top Values'!C11)"
             Worksheets("Top Value").Range("B142").Copy
             Worksheets("Top Value").Range("B142:D231").PasteSpecial xlPasteFormulas
               
            Worksheets("SteelSummary").Range("C14:E16").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!Q12:S14,0)"
           
            Worksheets("Discipline").Range("K6:K11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L63:L68,0)"
            Worksheets("Client Approved Cat").Range("K6:K8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L35:L37,0)"
            Worksheets("NUMBER OF RFQ MTOs").Range("I6:I7").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M73:M74,0)"
            Worksheets("Offers Validity Status").Range("L6:L11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M24:M29,0)"
            Worksheets("Currency BD").Range("N5:P25").FormulaArray = "=" & fullPath & "Report 1'!C54:E74"
            Worksheets("Currency BD").Range("Q5:Q25").FormulaArray = "=" & fullPath & "Report 1'!G54:G74"
       
        Case 10 'Equip
            Worksheets("Category BD").Range("J5:L20").FormulaArray = "=" & fullPath & "Report 1'!D81:F96"
           
             Worksheets("Top Value").Range("B40:D40").FormulaArray = "=" & fullPath & "Top Values'!C10:E10"
             Worksheets("Top Value").Range("E40:J130").FormulaArray = "=" & fullPath & "Top Values'!G10:L100"
             Worksheets("Top Value").Range("B41").Formula = _
                "=IF(" & fullPath & "Top Values'!C11=0,B40," & fullPath & "Top Values'!C11)"
             Worksheets("Top Value").Range("B41").Copy
             Worksheets("Top Value").Range("B41:D130").PasteSpecial xlPasteFormulas
           
            Worksheets("Discipline").Range("G6:G11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L63:L68,0)"
            Worksheets("Client Approved Cat").Range("G6:G8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L35:L37,0)"
            Worksheets("NUMBER OF RFQ MTOs").Range("F6:F7").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M73:M74,0)"
            Worksheets("Offers Validity Status").Range("H6:H11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M24:M29,0)"
            Worksheets("Currency BD").Range("H5:J25").FormulaArray = "=" & fullPath & "Report 1'!C54:E74"
            Worksheets("Currency BD").Range("K5:K25").FormulaArray = "=" & fullPath & "Report 1'!G54:G74"
       
        Case 11 'Linepipe
           Worksheets("Category BD").Range("C5:E20").FormulaArray = "=" & fullPath & "Report 1'!D81:F96"
           
            Worksheets("Top Value").Range("B2:D2").FormulaArray = "=" & fullPath & "Top Values'!C10:E10"
            Worksheets("Top Value").Range("E2:J33").FormulaArray = "=" & fullPath & "Top Values'!G10:L41"
           
             Worksheets("Top Value").Range("B3").Formula = _
                "=IF(" & fullPath & "Top Values'!C11="""",""STOP"",IF(" & fullPath & "Top Values'!C11=0,B2," & fullPath & "Top Values'!C11))"
            For Each cel In Worksheets("Top Value").Range("B3:B33")
                Worksheets("Top Value").Range("B3").Copy
                cel.Resize(1, 3).PasteSpecial xlPasteFormulas
                If IsError(cel.Value) Then cel.Value = "STOP"
                If cel.Value = "STOP" Then
                    Range(cel, Worksheets("Top Value").Range("D33")).ClearContents
                    Exit For
                End If
            Next
            Worksheets("SteelSummary").Range("C5:E8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!Q15:S18,0)"
            Worksheets("Discipline").Range("C6:C11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L63:L68,0)"
            Worksheets("Client Approved Cat").Range("C6:C8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L35:L37,0)"
            Worksheets("NUMBER OF RFQ MTOs").Range("C6:C7").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M73:M74,0)"
            Worksheets("Offers Validity Status").Range("D6:D11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M24:M29,0)"
            Worksheets("Currency BD").Range("B5:D25").FormulaArray = "=" & fullPath & "Report 1'!C54:E74"
            Worksheets("Currency BD").Range("E5:E25").FormulaArray = "=" & fullPath & "Report 1'!G54:G74"
            End Select
    Next
End With

    Worksheets("Master").Activate
    Range("F13").Activate
    MsgBox ("Done!")
    Application.ScreenUpdating = True

On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub }


one more which stuck in my mind as It read through B2:D2, range up to (D33) to get the source data but in case the source file if having data more than D33 let say D50 then we need to do Ctrl+D the code to increase the range to read from D33 to D50 from source file & Sir  we should have code which could get the data from source for column ("Top Values'!C11)  range from B3:D till the end of source file’s "Top Values'!C:E" is containing data & once the column E is blank it should stop/exit the loop.

I hope you got what I mean?

Thx
Avatar of byundt
byundt
Flag of United States of America image
Could you please repost the macro in a code block? Both text and formatting are completely messed up when you post in the body of a Comment.

You create a code block by clicking on the CODE icon at the top of the Comment box. This will put [ code ][ /code ] in the Comment box. Paste the code between the right bracket and the succeeding left bracket, e.g. [ code ]The macro code goes here[ /code]
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

Hi Brad,

As requested here is complete SUB,

 Sub BuildHyperlinksAndGetValues()
Dim flName As String, flPath As String, frmla As String, frmlaE As String, frmlaF As String, frmlaG As String, fullPath As String, _
    subFolder As String
Dim i As Long, iFirst As Long, iLast As Long
Dim cel As Range

Application.ScreenUpdating = False  'Makes macro run faster
Application.Calculation = xlCalculationManual   'Makes macro run faster
Application.DisplayAlerts = False
'On Error Resume Next

With Worksheets("Master")
    flPath = "\\share\Group\DEPT\GPO Bids\1. C411 Price\"
    subFolder = .[D$6] & "\" & .[$D$3] & " - " & .[$D$4] & "\" & "REV " & .[$D$5] & "\"     'no space before last \
    iFirst = 9
    iLast = 11

    For i = iFirst To iLast
        frmlaE = .[$D$3] & " - C411 - " & .[$D$4] & " " & .Cells(i, "D").Value & " Rev " & .[$D$5] & ".xlsb"
        fullPath = "'" & flPath & subFolder & "[" & frmlaE & "]"
        frmlaF = "=IFERROR(" & fullPath & "Report 1'!$G$48,0)"
        frmlaG = "=HYPERLINK(""" & flPath & subFolder & """&E" & i & ",""Link"")"     'Use when fully debugged
        
            'Use once fully debugged
        .Range("E" & i).Value = frmlaE
        .Range("F" & i).Formula = frmlaF
        .Range("G" & i).Formula = frmlaG
        
            'Put IFERROR formulas to retrieve data on worksheets: _
    Category BD, Top Value, SteelSummary, Discipline, Client Approved Cat, NUMBER OF RFQ MTOs, Offers Validity Status & Currency BD
        
        Select Case i
       Case 9 'Bulk
             Worksheets("Category BD").Range("Q5:S20").FormulaArray = "=" & fullPath & "Report 1'!D81:F96"
            
             Worksheets("Top Value").Range("B141:D141").FormulaArray = "=" & fullPath & "Top Values'!C10:E10"
             Worksheets("Top Value").Range("E141:J231").FormulaArray = "=" & fullPath & "Top Values'!G10:L100"
             Worksheets("Top Value").Range("B142").Formula = _
                 "=IF(" & fullPath & "Top Values'!C11=0,B141," & fullPath & "Top Values'!C11)"
             Worksheets("Top Value").Range("B142").Copy
             Worksheets("Top Value").Range("B142:D231").PasteSpecial xlPasteFormulas
                
            Worksheets("SteelSummary").Range("C14:E16").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!Q12:S14,0)"
            
            Worksheets("Discipline").Range("K6:K11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L63:L68,0)"
            Worksheets("Client Approved Cat").Range("K6:K8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L35:L37,0)"
            Worksheets("NUMBER OF RFQ MTOs").Range("I6:I7").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M73:M74,0)"
            Worksheets("Offers Validity Status").Range("L6:L11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M24:M29,0)"
            Worksheets("Currency BD").Range("N5:P25").FormulaArray = "=" & fullPath & "Report 1'!C54:E74"
            Worksheets("Currency BD").Range("Q5:Q25").FormulaArray = "=" & fullPath & "Report 1'!G54:G74"
        
        Case 10 'Equip
            Worksheets("Category BD").Range("J5:L20").FormulaArray = "=" & fullPath & "Report 1'!D81:F96"
            
             Worksheets("Top Value").Range("B40:D40").FormulaArray = "=" & fullPath & "Top Values'!C10:E10"
             Worksheets("Top Value").Range("E40:J130").FormulaArray = "=" & fullPath & "Top Values'!G10:L100"
             Worksheets("Top Value").Range("B41").Formula = _
                "=IF(" & fullPath & "Top Values'!C11=0,B40," & fullPath & "Top Values'!C11)"
             Worksheets("Top Value").Range("B41").Copy
             Worksheets("Top Value").Range("B41:D130").PasteSpecial xlPasteFormulas
            
            Worksheets("Discipline").Range("G6:G11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L63:L68,0)"
            Worksheets("Client Approved Cat").Range("G6:G8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L35:L37,0)"
            Worksheets("NUMBER OF RFQ MTOs").Range("F6:F7").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M73:M74,0)"
            Worksheets("Offers Validity Status").Range("H6:H11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M24:M29,0)"
            Worksheets("Currency BD").Range("H5:J25").FormulaArray = "=" & fullPath & "Report 1'!C54:E74"
            Worksheets("Currency BD").Range("K5:K25").FormulaArray = "=" & fullPath & "Report 1'!G54:G74"
       
        Case 11 'Linepipe
           Worksheets("Category BD").Range("C5:E20").FormulaArray = "=" & fullPath & "Report 1'!D81:F96"
            
            Worksheets("Top Value").Range("B2:D2").FormulaArray = "=" & fullPath & "Top Values'!C10:E10"
            Worksheets("Top Value").Range("E2:J33").FormulaArray = "=" & fullPath & "Top Values'!G10:L41"
            
             Worksheets("Top Value").Range("B3").Formula = _
                "=IF(" & fullPath & "Top Values'!C11="""",""STOP"",IF(" & fullPath & "Top Values'!C11=0,B2," & fullPath & "Top Values'!C11))"
            For Each cel In Worksheets("Top Value").Range("B3:B33")
                Worksheets("Top Value").Range("B3").Copy
                cel.Resize(1, 3).PasteSpecial xlPasteFormulas
                If IsError(cel.Value) Then cel.Value = "STOP"
                If cel.Value = "STOP" Then
                    Range(cel, Worksheets("Top Value").Range("D33")).ClearContents
                    Exit For
                End If
            Next
            Worksheets("SteelSummary").Range("C5:E8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!Q15:S18,0)"
            Worksheets("Discipline").Range("C6:C11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L63:L68,0)"
            Worksheets("Client Approved Cat").Range("C6:C8").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!L35:L37,0)"
            Worksheets("NUMBER OF RFQ MTOs").Range("C6:C7").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M73:M74,0)"
            Worksheets("Offers Validity Status").Range("D6:D11").FormulaArray = "=IFERROR(" & fullPath & "Report 1'!M24:M29,0)"
            Worksheets("Currency BD").Range("B5:D25").FormulaArray = "=" & fullPath & "Report 1'!C54:E74"
            Worksheets("Currency BD").Range("E5:E25").FormulaArray = "=" & fullPath & "Report 1'!G54:G74"
            End Select
    Next
End With

    Worksheets("Master").Activate
    Range("F13").Activate
    MsgBox ("Done!")
    Application.ScreenUpdating = True

On Error GoTo 0
Application.Calculation = xlCalculationAutomatic
Application.DisplayAlerts = True
End Sub

Open in new window




one more which stuck in my mind as It read through B2:D2, range up to (D33) to get the source data but in case the source file if having data more than D33 let say D50 then we need to do Ctrl+D the code to increase the range to read from D33 to D50 from source file & Sir  we should have code which could get the data from source for column ("Top Values'!C11)  range from B3:D till the end of source file’s "Top Values'!C:E" is containing data & once the column E is blank it should stop/exit the loop.

I hope you got what I mean?

Thx
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

Hope its fine now, Sorry for the inconvenience..
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

You are well come Sir but it is still the same repeating the STOP to blanks cells below & not clearing the “STOP”.

I run the same code with uncomment those two statements to do that with the file in which column D (column E in source workbook) have less data than 100 to see whether it will stop or not but instead it’s still the same repeating the STOP to blanks cells below & not clearing the “STOP” as well.

Im sorry if I have made some misunderstanding here, what Im trying to do here is code should read the column D (column E in source workbook) till upto data contains.
By putting D50 or to D100 is like set the limit for the user to can’t go beyond that, but most of time we have data more than limits set in source workbook ”Top Values” & in those cases I have to do manually CTRL+D to get the data even once I have more than 35,000 though.
Therefore looking for the infinity kind a thing.

Thx,
M-Zahid
ASKER CERTIFIED SOLUTION
Avatar of byundt
byundt
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

Hello Brad,
Yes that’s correct there is PivotTable below of that & I’m picking up the data form the pivot table as well & bcoz of the data increase I will change the pivot table to another sheets. Will work on the source workbook to get the less data as we can to accommodate in our REPORT file.

Thanks
M Zahid
Avatar of M Zahid
M Zahid
Flag of United Arab Emirates image

ASKER

Thanks once again Brad, you are the life saver…