Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Can anyone help me write this as a VB/excel macro.

I would like to select the following from one data sheet and write to another data sheet.

Select any rows where in column T the field contains the string SYDNEY-NEWC or contains the string F3 but does not contain F3 in the last 10 characters or where in column J the field contains the string SYDNEY-NEWC or contains the string M4 MTWY or contains the string F3 but does not contain the string F3 in the last 10 characters.

share|improve this question
    
I'm assuming you're writing from a SQL background... do you actually want to 'select' the rows or do you want to do something else with them (chart the data, move to another sheet etc.) ? Just selecting them is often inefficient in VBA. –  Gary Nov 30 '12 at 9:46
    
Hi I would like to select the data from one data sheet and write it to another data sheet. –  Alessandra Nov 30 '12 at 11:42
    
Great, thanks. Can you also just confirm the 'split' in your logic? I'm having trouble distinguishing which of your logical tests are for column T or column J. Perhaps you can split the tests onto several lines in your question? Thanks. –  Gary Nov 30 '12 at 11:47

1 Answer 1

This is a start. It's rough and ready, missing a lot of error checking and the LEN function will fall over if the cell contains less than 10 characters. But this will move across rows that match your pattern:

Sub MigrateData2()

Dim intRowCount As Integer
Dim intTargetRowCount As Integer
Dim intColumnCount As Integer

Dim CheckString1 As String
Dim CheckString2 As String

intTargetRowCount = 1

    For intRowCount = 1 To Range("A1").CurrentRegion.Rows.Count

        CheckString1 = Cells(intRowCount, 20) ' contents of column T
        CheckString2 = Cells(intRowCount, 10) ' contents of column J

        If CheckString1 = "SYDNEY-NEWC" Or InStr(CheckString1, "F3") _
          Or InStr(Len(CheckString1) - 10, CheckString1, "F3") > 0 _
          Or CheckString2 = "SYDNEY-NEWC" Or InStr(CheckString2, "M4 MTWY") _
          Or InStr(CheckString2, "F3") _
          Or InStr(Len(CheckString2) - 10, CheckString2, "F3") > 0 Then

            For intColumnCount = 2 To 20 ' change this last number to however many columns you have

              Sheets("Sheet2").Range("A1").Cells(intTargetRowCount, intColumnCount - 1).Value = _
              Sheets("Sheet1").Range("A1").Cells(intRowCount, intColumnCount).Value

            Next intColumnCount

            intTargetRowCount = intTargetRowCount + 1

        End If

    Next intRowCount

End Sub

ASSUMPTIONS:

  • Your source data is on Sheet1 and starts in cell A1
  • Your target sheet is Sheet2 and you want to start writing the data from cell A1
share|improve this answer
    
Hi Gary thank you so much. I will test this out and see how it goes. Alessandra –  Alessandra Nov 30 '12 at 19:22
    
Hi Gary Using your very helpful code as a strong base I came up with some code which I was going to post but I only have 491 characters left. –  Alessandra Dec 1 '12 at 6:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.