Excel frame a Do While Loop or macro

Solved/Closed
Report
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011
-
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011
-
Hi All,

Could anyone help me to frame a Do While Loop or a suitable macro? I need a method to copy formulas present in Cells F10 to Q10 in Sheet called "Tabular" to its below Cells (that is F11 to Q11, F12 to Q12 etc.) till it is equal to the number of rows containing Data in Cell A14, A15, A16 etc. of Sheet called "Data". In short formulas to be copied in "Tabular" Sheet should be equivalent to the number of Rows in "Data" Sheet.

Please refer to the attached file. Here there are 86 data rows in "Data" Sheet, so the formula to be copied in "Tabular" Sheet is A96 to R96. The data in "Data" Sheet will not be constant always; it will vary time to time.

Regards,
Pramod



8 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
What attached file ?
Could you please upload a sample file with sample data etc on some shared site like https://authentification.site and post back here the link to allow better understanding of how it is now and how you foresee.
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011

Hi, thanks for your quick response. After drafting the message only I realized there is no way to upload a file. Please find the download link: https://authentification.site/files/22313708/ForumHelp.xls
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Try this
Sub CopyFormula()
Dim lMaxRows As Long

    On Error Resume Next
        lMaxRows = 0
        lMaxRows = Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    On Error GoTo 0
    
    
    If lMaxRows > 10 Then
        Range("F10:Q10").Select
        Selection.AutoFill Destination:=Range("F10:Q" & lMaxRows), Type:=xlFillDefault
        
    End If
    
End Sub
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011

Hello,

This code is not working with me; I did not see reference of any sheet names in the Code this may be reason for nonfunctioning. Could you please recheck and post your comments.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It is presuming that the sheet where the formula is the active sheet
I tested with the sheet that you provided. What is the error. Hard to tell with "this code is not working with me". Ensure that the sheet on which you want this operation to occur is selected and if you still get an error, you would need to share the file that has this issue.
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011

Please find the file (https://authentification.site/files/22327469/ForumHelp2.xls
) in which the Macro is added as "Macro2()", there is no action seen after running the Macro. It will be highly appreciated if you could help me solving this issue.

Thanks & Regards,
PramodKumar
Posts
47367
Registration date
Monday February 1, 2010
Status
Moderator
Last seen
September 1, 2021
11,252
Hello,
Please refrain from writing your e-mail, you are compromising your system's security.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
It is working as designed. The design was bad. I misread on which sheet the row count was based on. It was using rows from "Tabular Data". You I think had meant only "Data". Use this modified version

Sub CopyFormula() 

Dim lMaxRows As Long 
Dim lStartRow As Long 

    On Error Resume Next 
        lMaxRows = 0 
        lStartRow = 0 
        lMaxRows = Sheets("Data").Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 
        lStartRow = Sheets("Data").Cells.Find("*", Cells(1, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row 
    On Error GoTo 0 
     
     
    If (lMaxRows - lStartRow > 0) Then 
        Sheets("Tabular Data ").Select 
        Range("F10:Q10").Select 
        Selection.AutoFill Destination:=Range("F10:Q" & lMaxRows - lStartRow), Type:=xlFillDefault 
         
    End If 
     

End Sub 
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011

Thank you very much for your help. This working good but it is copying number of Rows in "Data" Sheet minus 9 Rows, that is 9 rows lesser than the total rows in "Data" Sheet. Can you please correct this also?

With Regards,
PramodKumar.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
You said based on used rows; In your example the data starts at row 13 and ends at row 116. So there are about 103 rows. Are you saying that there should be 115 rows ?
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011

Thank you for your continued selfless help to my many doubts. The Rows conflict is resolved when I changed last line as: (Selection.AutoFill Destination:=Range("F10:Q" & lMaxRows), Type:=xlFillDefault) that is "- lStartRow" is removed. I would like to request you to check the compile error message I am getting while populating formulas in Sheets. The Error is: Compile Error: Duplicate Declaration in current scope

Please check the Code I am using to do this job (world file, URL: https://authentification.site/files/22331243/ForumHelp_Code.doc


Thanks & Regards,
PramodKumar
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
The error explains it self "Duplicate Declaration "

Dim is used to declare variable.
In a sub routine, you can declare same variable only once

In the code you had this on at least two occasion.
Dim lMaxRows As Long
Dim lStartRow As Long

It it still does not resolve your issue, please post your excel book with code
Posts
19
Registration date
Friday May 7, 2010
Status
Member
Last seen
October 4, 2011

Hi,

I am able to overcome the error when I placed

Dim lMaxRows As Long
Dim lStartRow As Long

at the beginning of the codes (that is before "Sub") and removed these from the codes in "Sub". Thank you once again for your help and support. Hope to continue a part of the Forum.

With regards,
PramodKumar