none
VB help please - moving 3 cells (in Excel) and then relocating to another 3 cells - then... RRS feed

  • Question

  • Hi Everyone:

    I need some help please. (in VB)

    [To start] I have numbers(data) in 3 Excel cells - in the cells beside each other. (Let's say C1, D1 and E1)

    I want to move that data to another location (call it C5,D5 and E5)on the spread sheet.

    Then, 3 more sets of data will occur and I want to move that data to the same location  (C5, D5 and E5) BUT if there is data (any data) in those 3 cells, I want (then) to put the new data into the next 3 cells, which would be C6, D6 and E6.    I do not wish to overwrite the data in cells C5, D5 and E5 and I want to be able to view the view the data in C1, D1 and E1 and in C5, D5 and E5.

    This process may continue for a 100, maybe 200 or more cycles - and I need to see the data and not have it overwrite prior data.

    If anyone would offer a bit of help, I would be appreciative.

    Thanks.


    Wednesday, December 31, 2014 8:04 PM

Answers

  • Hi,

    Thanks for posting in MSDN forum.

    Based on the description, you want to move the data to another position if there is no values in the target cells.

    We can use Range.Value to determine whether there is data in the cell, here is a sample that copy the C1, D1 and E1 to the target range, if there is no data in the target range:

      Sub Main()
            Dim ExcelApp As New Application
            ExcelApp.Visible = True
            Dim aWorkBook As Workbook
            aWorkBook = ExcelApp.Workbooks.Open("C:\Users\UserName\Desktop\book1.xlsm")
    
            aWorkBook.Worksheets("Sheet1").Activate()
    
            Dim targetRange As Range
            Dim targetRowIndex As Integer
            targetRowIndex = 5
            targetRange = aWorkBook.Worksheets("Sheet1").Range("C" & targetRowIndex & ":E" & targetRowIndex)
    
            While Not IsEmpty(targetRange.Cells(1)) Or Not IsEmpty(targetRange.Cells(2)) Or Not IsEmpty(targetRange.Cells(3))
                targetRowIndex = targetRowIndex + 1
                targetRange = aWorkBook.Worksheets("Sheet1").Range("C" & targetRowIndex & ":E" & targetRowIndex)
            End While
    
            aWorkBook.Worksheets("Sheet1").Range("C1:E1").Cut()
            targetRange.Cells(1).Activate()
            aWorkBook.Worksheets("Sheet1").Paste()
        End Sub
    
        Function IsEmpty(aCell As Range) As Boolean
            If aCell.Value Is Nothing Then
                IsEmpty = True
            Else
                IsEmpty = aCell.Value.ToString() = ""
            End If
        End Function

    You can get more detail about Excel PIAs from link below:
    Welcome to the Excel 2013 Primary Interop Assembly Reference

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, January 2, 2015 7:08 AM
    Moderator