Previous Topic Next Topic

Automate The Process

Closed
Avy - Sep 17, 2010 at 02:52 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Oct 17, 2010 at 01:03 PM
Hello,

I have this problem on how to make this in to just one procedure.

Range is C:C

now as you can see it selects a range and copy its data and paste it to the next empty cells
Is it possible to do this is with one procedure that will copy and paste? It will stop at the last empty cell or C1000 to be exact, just to control the paste thing.

Range("C2").Select ----DATA TO BE COPIED
Selection.Copy
Range("C3").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C3:C11").Select ------NEXT EMPTY CELL BELOW
ActiveSheet.Paste
Application.CutCopyMode = False

Selection.End(xlDown).Select ---- ANOTHER DATA TO BE COPIED AND SO ON....
Selection.Copy
Range("C15").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C15:C17").Select ------NEXT EMPTY CELL BELOW
ActiveSheet.Paste
Application.CutCopyMode = False


Selection.End(xlDown).Select
Selection.Copy
Range("C19").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C19:C22").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Selection.End(xlDown).Select
Selection.Copy
Range("C24").Select
Range(Selection, Selection.End(xlDown)).Select
Range("C24:C29").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Done until the last data to be copied.
Any help is good.
Please advise me what to do with this.
Thanks
Related:

5 responses

Answer 1 / 5
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Sep 19, 2010 at 07:56 AM
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc and post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too
0
Answer 2 / 5
avy
Sep 19, 2010 at 09:13 PM
Hi,

What Im trying to do is like this:


data1
blank cell
blank cell
data2
blank cell
blank cell
blank cell
data3
blank cell

output should be
data1
data1
data1
data2
data2
data2
data2
data3
data3

it will just copy the data on the next blank cell(downward). after it paste the data1 it will proceed to data2 and so on.

This is what Im trying to do. I have no idea how to put this on loop.
Thanks
0
Answer 3 / 5
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Sep 20, 2010 at 08:47 AM
if all you want to do is fill in the missing value with the last value, then you can try this 

    Dim lRow As Long
    Dim lLastRow As Long
    Dim lLastRow As Long
    
    lLastRow = Cells(Rows.Count, "C").End(xlUp).Row
    If (Cells(Rows.Count, "C") <> vbNullString) Then lLastRow = Rows.Count
    lLastRow = 1
    For lRow = 1 To lLastRow
        If Cells(lRow, "C") <> Cells(lLastRow, "C") _
        Then
            lLastRow = lRow
        ElseIf (Cells(lRow, "C") = vbNullString) _
        Then
            Cells(lRow, "C") = Cells(lLastRow, "C")
        End If
    Next lRow
0
Answer 4 / 5
Avy
Sep 20, 2010 at 09:52 PM
Having errors in putting the copy and paste command. Where should I put it in the Loop?

I put the copy command in the first if loop. the trouble is in the paste thing.

Thanks


/////if all you want to do is fill in the missing value with the last value, then you can try this

Dim lRow As Long
Dim lLastRow As Long
Dim lLastRow As Long

lLastRow = Cells(Rows.Count, "C").End(xlUp).Row
If (Cells(Rows.Count, "C") <> vbNullString) Then lLastRow = Rows.Count
lLastRow = 1
For lRow = 1 To lLastRow
If Cells(lRow, "C") <> Cells(lLastRow, "C") _
Then
lLastRow = lRow
ElseIf (Cells(lRow, "C") = vbNullString) _
Then
Cells(lRow, "C") = Cells(lLastRow, "C")
End If
Next lRow
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Sep 21, 2010 at 01:54 AM
what error?

here is the full routine 
Sub ReplicateCell()  
    Dim lRow As Long  
    Dim lLastRow As Long  
    Dim lMaxRow As Long  
    Dim Cell As Range  
      
    Set Cell = Cells.Find("*", Cells(1, 1), , , xlByRows, xlPrevious)  
    If Cell Is Nothing Then Exit Sub  
    lMaxRow = Cell.Row  
    Set Cell = Nothing  
    lLastRow = 1  
    For lRow = 1 To lMaxRow  
        If (Cells(lRow, "C") = vbNullString) _  
        Then  
           Cells(lRow, "C") = Cells(lLastRow, "C")  
        ElseIf Cells(lRow, "C") <> Cells(lLastRow, "C") _  
        Then  
            lLastRow = lRow  
        End If  
    Next lRow  
End Sub
0

Didn't find the answer you are looking for?

Ask a question
Answer 5 / 5
Avy
Sep 29, 2010 at 09:21 PM
sorry for late reply.

Im talking about the copy and paste routine, Where will I insert it?

Thanks
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Oct 17, 2010 at 01:03 PM
that was supposed to replace your existing code. if my memory serves me for a change, you had a routine by that name
0

Similar discussions

an error occured, please try again later
adrian -
Farman -

35 responses
Sound picker cached background process
Badchad -
Ambucias -

3 responses
avg process manager
glad2cu2k6 -
glad2cu2k6 -

2 responses
error occurred on yutube
Kamrul Emam -
Kamrul Emam -

2 responses
I have completed the task they ask me to do but still yet th
Chriskerry -
HelpiOS -

1 response