Previous Topic Next Topic

Excel frame a Do While Loop or macro

Solved/Closed
madambath Posts 19 Registration date Friday May 7, 2010 Status Member Last seen October 4, 2011 - May 7, 2010 at 05:23 AM
madambath Posts 19 Registration date Friday May 7, 2010 Status Member Last seen October 4, 2011 - May 9, 2010 at 11:40 AM
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



Related:

8 responses

Answer 1 / 8
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 05:51 AM
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.
0
Answer 2 / 8
madambath Posts 19 Registration date Friday May 7, 2010 Status Member Last seen October 4, 2011
May 7, 2010 at 06:04 AM
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
0
Answer 3 / 8
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 08:45 AM
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
0
Answer 4 / 8
madambath Posts 19 Registration date Friday May 7, 2010 Status Member Last seen October 4, 2011
May 7, 2010 at 02:54 PM
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 7, 2010 at 03:33 PM
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.
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 8
madambath Posts 19 Registration date Friday May 7, 2010 Status Member Last seen October 4, 2011
May 8, 2010 at 05:30 AM
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
0
Ambucias Posts 47356 Registration date Monday February 1, 2010 Status Moderator Last seen February 15, 2023 11,171
May 8, 2010 at 05:45 AM
Hello,
Please refrain from writing your e-mail, you are compromising your system's security.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 8, 2010 at 06:24 AM
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
0
Answer 6 / 8
madambath Posts 19 Registration date Friday May 7, 2010 Status Member Last seen October 4, 2011
May 8, 2010 at 06:39 AM
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.
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 8, 2010 at 07:31 AM
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 ?
0
Answer 7 / 8
madambath Posts 19 Registration date Friday May 7, 2010 Status Member Last seen October 4, 2011
May 8, 2010 at 09:32 AM
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
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
May 8, 2010 at 11:18 AM
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
0
Answer 8 / 8
madambath Posts 19 Registration date Friday May 7, 2010 Status Member Last seen October 4, 2011
May 9, 2010 at 11:40 AM
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
0

Similar discussions

How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses