+ Reply to Thread
Results 1 to 3 of 3

Copy cells from range "C3" to "F3" with loop and blank field

  1. #1
    Registered User
    Join Date
    12-16-2015
    Location
    Brussels
    MS-Off Ver
    2010 Standart
    Posts
    2

    Copy cells from range "C3" to "F3" with loop and blank field

    Hi ,
    I have recorded this macro ,but I do not know how many times I do have to repeat this I am quite new to VBA for excel
    so I try to explain to you what I would like.
    I have a xml file , which I inject into a excel 2010 file so far so good , but the lay-out is wrong I only need the "C" row because there are the info which I need , but in the wrong order , it always starts at "C3"
    and I also need the next field "C4" .
    but I need "C3" on "F3" and "C4" on "G3" next to each other and then there is a blank "C5" ( which I do not need)
    but I need "C6" on "F6" and "C7" on "G6" and "C8" is a blank again and so on
    and this until the end which could be anything , at one point the "C" row will be empty , so something like loop it until 3 blank fields on "C"
    would do the trick.
    I have recorded something like this
    Range ("C3").Select
    Selection.Copy
    Range ("F3").Select
    ActiveSheet.Paste
    Range ("C4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range ("G3").Select
    ActiveSheet.Paste
    Range ("C6").Select
    Selection.Copy
    Range ("F6").Select
    ActiveSheet.Paste
    Range ("C7").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range ("G6").Select
    ActiveSheet.Paste

    and so on
    but looping it with always 1 blank in between is not so easy for me , and just keep recording the macro until 2000 or 3000 times
    I think the must be an easier way, I have been try and looking it up via google , but couldn't find anything which fits my needs
    so if someone has an idea this would be nice thanks
    Last edited by masterm; 12-16-2015 at 01:04 PM. Reason: solved

  2. #2
    Forum Expert skywriter's Avatar
    Join Date
    06-09-2014
    Location
    USA
    MS-Off Ver
    365 Version 2409
    Posts
    2,789

    Re: Copy cells from range "C3" to "F3" with loop and blank field

    Attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

    Remember to desensitize the data.

    Click on GO ADVANCED and use the paperclip icon to open the upload window.

    View Pic

  3. #3
    Registered User
    Join Date
    12-16-2015
    Location
    Brussels
    MS-Off Ver
    2010 Standart
    Posts
    2

    Re: Copy cells from range "C3" to "F3" with loop and blank field

    solved

    Sub RedesignLayout()
    ' Defines variables
    Dim Cell As Range, cRange As Range
    ' Defines LastRow as first row of column F below headers (assumes there is a header value in F2)
    LastRow = ActiveSheet.Cells(Rows.Count, "F").End(xlUp).Row + 1
    ' Sets range to check as C3:C20 (update as required)
    Set cRange = Range("C3:C20")
    ' For each cell in check range
    For Each Cell In cRange
    ' If the cell value is not blank then...
    If Cell.Value <> "" Then
    ' Copy the cell
    Cell.Copy
    ' If column F and LastRow is blank then paste to it
    If Range("F" & LastRow).Value = "" Then
    Range("F" & LastRow).Select
    ActiveSheet.Paste
    ' Else if column F and LastRow is not blank then paste to G and LastRow
    Else: Range("G" & LastRow).Select
    ActiveSheet.Paste
    ' Increase LastRow by 2 to account for both F and G being pasted to
    LastRow = LastRow + 2
    End If
    End If
    ' Check next cell in check range
    Next Cell
    End Sub




    also this is working thanks anyways

    Sub C3()
    Dim i As Long, lr As Long
    lr = Range("C" & Rows.Count).End(xlUp).Row
    For i = 3 To lr Step 3
    Range("C" & i).Offset(0, 3).Value = Range("C" & i).Value
    Range("C" & i).Offset(0, 4).Value = Range("C" & i).Offset(1, 0).Value
    Next i
    End Sub
    thanks

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Replace all BLANK cells in column with header title "Balance" to "0"
    By ks100 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-21-2014, 09:25 AM
  2. Replies: 5
    Last Post: 01-23-2014, 11:02 AM
  3. Replies: 4
    Last Post: 11-17-2013, 12:05 PM
  4. [SOLVED] How to USE """"" cells count """"" change font color
    By austin123456 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-09-2013, 06:14 AM
  5. Replies: 2
    Last Post: 06-06-2013, 12:45 PM
  6. Replies: 3
    Last Post: 02-16-2011, 02:55 PM
  7. How can i copy value from "HTMLText"(EMBED("Forms.HTML:Text","")),using Macro
    By andrewyang in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-18-2010, 12:47 AM
  8. Replies: 3
    Last Post: 12-14-2006, 01:36 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1