Basic Question from Rookie...

[NeilATaylor]

I have got my Macro to the stage where it now presents me with two sheets of data. A kind of transaction master file (downloaded from Finance system by intended user) and a modified transaction master file, manipulated by the macro, with a TRUE/FALSE identifier. What I want the Macro to do is quite simple....

1. Lookup column header "transaction number" in modified transaction master file and return value in column C, either True or False, in last column of active sheet. Keeping in mind that column numbers will vary, and position of "transaction number" column will change between users.

2. For all rows where "transaction number" returns True (as per step above, I want the Macro to Cut and Paste these rows into a separate sheet. Can this be done using the macro?

If possible, could replies be kept as two separate macros for each step (1 & 2) above? might make it easier for me to go through them.

Cheers
Neil

 

[al_b_cnu]

Hi,

For requirement 1, not too sure about:
a) do you want True / False in column C or in 1st free column
b) What are the conditions for True / False to be returned?

What are the sheet names used?

For requirement 2, do you want the paste to be appended to, or to overwrite any existing data?

 

[NeilATaylor]

Re-reading my 1st post. wasn't that clear! I'll make it a bit easier to follow.

Two sheets, 1 and 2. Both have transaction number, unique reference.

1. In Sheet 1, look up "Transaction Number" in sheet 2 (column C - either "True" or "False"). Return values from column C to sheet 1 to cell in same row - last empty column. There could be any number of columns as the number off columns will change between users.

2. After the Macro has performed the Lookup, as per above, Sheet 1 will now have any extra column with values "True or False". Any rows which have the value "TRUE" I want to cut and paste to a new worksheet.

Eg, Sheet 1:
T TT Trans Number Trans Date Period

B GA 25014312 05/07/06 200603
B GA 25014313 05/07/06 200604
B GA 25014667 17/07/06 200604
B GA 25014843 03/08/06 200604


Sheet 2:
Trans Number Amount Value T/F
25014667 2196.97 FALSE
25014843 -9090.91 TRUE
25014844 9090.91 TRUE

In Sheet 1, I want to look up "Trans Number" in sheet 2, find True or False, all True cut and paste to a new sheet in same workbook...


Thanks again for your help!
Cheers
Neil

 

[al_b_cnu]

Hi Neil, Part 1 code:

Option Explicit

Sub SetTorF()
Const sLookupHeading As String = "Trans Number"
Dim bTorF As Boolean
Dim iColTransNum As Integer, iLastCol As Integer
Dim lRow As Long, lRowEnd As Long, lLookupRow As Long, lOutputPtr As Long
Dim vaInputData As Variant, vaOutputData() As Variant
Dim wsFrom As Worksheet, wsLookup As Worksheet

'-- Set sheet names --
Set wsFrom = Sheets("Sheet1")
Set wsLookup = Sheets("Sheet2")

'-- Calculate last column number plus 1 --
iLastCol = wsFrom.Cells(1, Columns.Count).End(xlToLeft).Column + 1

'-- find 'Trans Number' heading in sheet 1 --
iColTransNum = 0
On Error Resume Next
iColTransNum = WorksheetFunction.Match(sLookupHeading, wsFrom.Rows(1), 0)
On Error GoTo 0
If iColTransNum = 0 Then
    MsgBox prompt:="Heading '" & sLookupHeading & "' not found in sheet " & wsFrom.Name, _
            Buttons:=vbOKOnly + vbCritical
    Exit Sub
End If

'-- find last row containing data in sheet1 --
lRowEnd = wsFrom.Cells(Rows.Count, iColTransNum).End(xlUp).Row

If lRowEnd < 2 Then
    MsgBox prompt:="Sheet '" & wsFrom.Name & "' has no data!", _
            Buttons:=vbOKOnly + vbCritical
    Exit Sub
End If

'-- Store Trans Number values into input array --
vaInputData = wsFrom.Range(Cells(1, iColTransNum).Address, _
                           Cells(lRowEnd, iColTransNum).Address).Value

'-- Initialise output array --
ReDim vaOutputData(1 To UBound(vaInputData, 1), 1 To 1)
vaOutputData(1, 1) = "T/F"

'-- Now loop thru array, extracting trans numbers & check against sheet 2 --
For lRow = 2 To UBound(vaInputData, 1)
    If Len(CStr(vaInputData(lRow, 1))) <> 0 Then
    
        '-- find match on next tran number --
        lLookupRow = 0
        On Error Resume Next
        lLookupRow = WorksheetFunction.Match(vaInputData(lRow, 1), wsLookup.Columns("A"), 0)
        On Error GoTo 0
        
        '-- If match, store True / False into output array --
        If lLookupRow <> 0 Then vaOutputData(lRow, 1) = CBool(wsLookup.Cells(lLookupRow, 3).Value)
    End If
Next lRow

'-- Store output array into sheet1 --
wsFrom.Range(Cells(1, iLastCol).Address, _
             Cells(UBound(vaOutputData, 1), iLastCol).Address).Value = vaOutputData
End Sub

 

[al_b_cnu]

and here's part 2 code:

Sub PasteTrueData()
Dim iLastCol As Integer, iCol As Integer
Dim lRowEnd As Long, lRow As Long, lOutput As Long
Dim vaInput As Variant, vaOutput() As Variant
Dim wsFrom As Worksheet, wsTo As Worksheet

'-- Set sheet names --
Set wsFrom = Sheets("Sheet1")
Set wsTo = Sheets("Sheet3")

'-- Get last column of input (contains 'True/False') --
iLastCol = wsFrom.Cells(1, Columns.Count).End(xlToLeft).Column

'-- Get last row of input data --
lRowEnd = wsFrom.Cells(Rows.Count, iLastCol).End(xlUp).Row

lOutput = 1

If lRowEnd > 1 Then
    '-- Store input data into array --
    vaInput = wsFrom.Range("A1", Cells(lRowEnd, iLastCol).Address).Value
    
    '-- Initialise output array (NOTE - TRANSPOSED!) --
    ReDim vaOutput(1 To iLastCol, 1 To 1)
    For iCol = 1 To iLastCol
        vaOutput(iCol, 1) = vaInput(1, iCol)
    Next iCol
    
    '-- Loop thru array & extract all 'True' entries --
    For lRow = 2 To UBound(vaInput, 1)
        '-- If 'True', extend the output array & copy data to it --
        '-- Note that the output array is Transposed!            --
        If CBool(vaInput(lRow, iLastCol)) Then
            lOutput = lOutput + 1
            ReDim Preserve vaOutput(1 To iLastCol, 1 To lOutput)
            For iCol = 1 To iLastCol
                vaOutput(iCol, lOutput) = vaInput(lRow, iCol)
            Next iCol
        End If
    Next lRow
End If

If lOutput < 2 Then
    MsgBox prompt:="Sheet '" & wsFrom.Name & "' has no data to transfer", _
            Buttons:=vbOKOnly + vbCritical
Else
    '-- Clear output worksheet & store array into it --
    With wsTo
        .Cells.ClearContents
        .Range("A1", Cells(UBound(vaOutput, 2), iLastCol).Address).Value = _
                        WorksheetFunction.Transpose(vaOutput)
    End With
End If
End Sub

 

[NeilATaylor]

Wooah! Thats excellent! I'm going to read through this quite carefully and study how it works, needless to say it does work brilliantly.
One more question, what would be the macro code to delete all the transactions in sheet 1 that are TRUE? (they have been exported to Worksheet 3...). That way I would have one worksheet with just FALSE (worksheet 1) and one worksheet with all True (worksheet 3).
Thanks again!
Cheers
Neil

 

[NeilATaylor]

Sorry, when I say transactions, what I mean to say is how do I delete Rows where the value in column T/F is TRUE, for worksheet 1.

 

[NeilATaylor]

Don't worry, I found this on another website:

Macro11

Sheets("Sheet1").Select
Dim rng As Range
Dim what As String
what = "TRUE"
Do
Set rng = ActiveSheet.UsedRange.Find(what)
If rng Is Nothing Then
Exit Do
Else
Rows(rng.Row).Delete
End If
Loop
End Sub

Thanks! Neil.