Posts by MattChoo

Re: INDEX/MATCH Lookup for only visible text cells

Quote from NBVC;785391

Try this array* formula:

=IFERROR(INDEX('ICE Incoming'!$P$2:$P$5000,SMALL(IF(((SUBTOTAL(3,OFFSET('ICE Incoming'!$B$2:$B$5000,ROW('ICE Incoming'!$B$2:$B$5000)-MIN(ROW('ICE Incoming'!$B$2:$B$5000)),,1))>0)*('Supplier Performance'!$AG$5='ICE Incoming'!$B$2:$B$5000)),ROW('ICE Incoming'!$B$2:$B$5000)-MIN(ROW('ICE Incoming'!$B$2:$B$5000))+1),ROW(A1))),"")

[ARF]x[/ARF]

Thank you very much - works like a charm

Hi Guys,

So i'm trying to return data from a table using this array formula - the Formula works like a charm but I need it to pickup when the data table has been filtered. I've been trying to use SUBTOTAL but I cannot get it it return text values, only numbers.

Here is my formula:

{=IFERROR(INDEX('ICE Incoming'!$P$2:$P$5000,SMALL(IF('Supplier Performance'!$AG$5='ICE Incoming'!$B$2:$B$5000,ROW('ICE Incoming'!$B$2:$B$5000)-MIN(ROW('ICE Incoming'!$B$2:$B$5000))+1,""),ROW(A1))),"")}

Column P is what I want returning, column B has the criteria values in, AG5 is the criteria.

Hope that makes sense.

Hi Guys,

I have two tables with sets of various data that i'd like to bring together. They both have unique ID numbers but one table has zeros after "PD".
I need to drop the zeros after the PD and perform a vlookup.

EG.

[TABLE="width: 256"]

[TD="colspan: 2"]256PD0000001138[/TD]

256PD1138

[TD="colspan: 2"]256PD0000001106[/TD]

256PD1106

[TD="colspan: 2"]SACM0510PD00002[/TD]

SACM0510PD2

[TD="colspan: 2"]62PD00000002474[/TD]

62PD2474

[TD="colspan: 2"]62pd00000002482[/TD]

62pd2482

[TD="colspan: 2"]241PD0000000135[/TD]

241PD135

[TD="colspan: 2"]241PD0000000077[/TD]

241PD77

[TD="colspan: 2"]100PD0000000036[/TD]

100PD36

[TD="colspan: 2"]256PD0000001160[/TD]

256PD1160

[TD="colspan: 2"]258PD0000000004[/TD]

258PD4

[TD="colspan: 2"]63PD00000004095[/TD]

63PD4095

[TD="colspan: 2"]62PD00000002487[/TD]

62PD2487

[TD="colspan: 2"]256PD0000000922[/TD]

256PD922

[/TABLE]
[TABLE="width: 128"]

[TD="width: 64"][/TD]
[TD="width: 64"][/TD]

[/TABLE]

Re: Copy data from multiple tabs in workbook to one single tab in another workbook

Thank you sir.

Hi Guys,

Having a bit of trouble here, i'm trying to copy data from multiple tabs in one workbook to one single tab in another workbook.
I've got the code in principle but it's over-writing each loop then all I get at the end is the final tab data, instead of all data appended.

I have this code run when the workbook opens, all the column headers are the same in each tab......
Hope i've been clear.

Private Sub Workbook_Open()
Workbooks.Open Filename:="X:\Production\PS4\dt.xlsx", ReadOnly:=True
'Workbooks("dt.xlsx").Activate
Dim ws As Worksheet
    
    Application.ScreenUpdating = False
     
    For Each ws In Workbooks("dt.xlsx").Sheets(Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec"))
        With ws
            .Range("A2:BZ1000").Copy
            Workbooks("Value Stream.xlsm").Worksheets("Summary").Range("A" & Cells(Rows.Count, 1).End(xlUp).Row + 1).PasteSpecial (xlPasteValues)
        End With
    Next ws
     
    Application.CutCopyMode = False
    Application.ScreenUpdating = True




End Sub

Re: Make multiple cell changes whilst logging changes in .txt file

As a quick test, it appears to capture and log the data correctly.

You sir are a gentleman & a scholar. I have been racking my brains for quite a few hours with that conundrum.

Regards
Matt

Re: Make multiple cell changes whilst logging changes in .txt file

Ok, i'm with you. It's tracking multiple changes now, but the previous value is not looping, only the current.

I had "123" in cell O13. That is correct
I had "456" in cell P14. The log is incorrect.

P13 & O14 were blank.

14-Aug-15 14:08:24 Matthew Schofield changed cell $O$13 in Sheet2 from '123' to '' in workbook C:\Users\mschofield\Desktop Example Code.xlsm
14-Aug-15 14:08:24 Matthew Schofield changed cell $P$13 in Sheet2 from '123' to '' in workbook C:\Users\mschofield\Desktop Example Code.xlsm
14-Aug-15 14:08:24 Matthew Schofield changed cell $O$14 in Sheet2 from '123' to '' in workbook C:\Users\mschofield\Desktop Example Code.xlsm
14-Aug-15 14:08:24 Matthew Schofield changed cell $P$14 in Sheet2 from '123' to '' in workbook C:\Users\mschofield\Desktop Example Code.xlsm

Re: Make multiple cell changes whilst logging changes in .txt file

Thank you, Stephen

This has certainly stopped the error, although multiple changes are not being logged into the file......

Hi All,

I have this piece of code that will log changes that are made to cells into a .txt file located in the same place as the workbook.

The problem i'd like to overcome is how to log the changes of multiple cells at the same time.... I.e selecting A1:A5 and hitting delete......
Current code will bug out, so I have a message box appear to warn the user then undo the changes.......

If anyone can guide me here it would be much appreciated.

Code below, and workbook attached.

Option Explicit
Dim PreviousValue




Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sLogFileName As String, nFileNum As Long, sLogMessage As String




    sLogFileName = ThisWorkbook.Path & Application.PathSeparator & "Log.txt"




'checks whether multiple cells are changed, if so, undo last action *Not ideal*
If Target.Count > 1 Then
    MsgBox "Cannot log changes when updating multiple cells at the same time"
     With Application
        .EnableEvents = False
        .Undo
        .EnableEvents = True
    End With
    Exit Sub
End If




 On Error Resume Next ' Turn on error handling
 'Check all cells for changes, excluding D4 D5 E5 M1 etc
    If Target.Value <> PreviousValue And Intersect(Target, Range("D4,D5,E5,m1")) Is Nothing Then
        ' Check if we have an error
           If Err.Number = 13 Then
           PreviousValue = 0
           
        End If
        ' Turn off error handling
        On Error GoTo 0
        'log data into .txt file
        sLogMessage = Now & " " & Application.UserName & " changed cell " & Target.Address _
        & " in " & ActiveSheet.Name & " from " & "'" & PreviousValue & "' to '" & Target.Value & "'" & " in workbook " & ThisWorkbook.Path & " " & ActiveWorkbook.Name




        nFileNum = FreeFile                         ' next file number
        Open sLogFileName For Append As #nFileNum   ' create the file if it doesn't exist
        Print #nFileNum, sLogMessage                ' append information
        Close #nFileNum                             ' close the file
    End If
    
    
End Sub




Private Sub Worksheet_SelectionChange(ByVal Target As Range)




'looks at the uppermost cell (incase cells are merged)
PreviousValue = Target(1).Value




End Sub

Regards
Matt

Re: Worksheet with command button

You can try deleting the temporary MSForms.exd files just once in these hidden temp folders:

C:\Users\[user.name]\AppData\Local\Temp\Excel8.0\MSForms.exd
C:\Users\[user.name]\AppData\Local\Temp\VBE\MSForms.exd

Regards
Matt

Hi Guys,

As the title says, I am having trouble with this. This is the formula I have so far:

=SUMPRODUCT(('SPC-103R.xlsm'!Table1[Alloy]=A3)*('SPC-103R.xlsm'!Table1[Mesh Type]=B3)*('SPC-103R.xlsm'!Table1[Flux Type]=C3)*('SPC-103R.xlsm'!Table1[METAL%]=E3)*('SPC-103R.xlsm'!Table1[IPN]))

Here's the table I reference.

A3 B3 C3 E3

Sn62 Type 3 NC-SMQ92H 90

[TABLE="width: 637"]

IPN

Low Malcom

High Malcom

Low Metal% Shipping

High Metal % Shipping

Alloy

Mesh Type

Flux Type

METAL%

81484

1850

2450

90.25

90.75

Sn63

Type 3

RMA-SMQ51AC

90.5

81501

1525

2025

89.75

90.25

Sn63

Type 3

RMA-SMQ51AC

90

81506

1800

2400

90.25

90.75

Sn62

Type 3

RMA-SMQ51AC

90.5

81609

1850

2450

90.25

90.75

Sn63

Type 3

RMA-SMQ51AC

90.5

81648

1850

2450

90.25

90.75

Sn63

Type 3

NC-SMQ51SC

90.5

[/TABLE]

Re: Matching a month and a team

This should do the trick.

=SUMIFS(Input!D13:D45,Input!B13:B45,">=" & B1,Input!B13:B45,"<=" & C1)

Amend as appropriate

Re: Toggle button to refresh formulas

Hello,

This should work for you I imagine. Although I believe 5 seconds is a pretty frequent interval and suggest a longer one, or a button to trigger refresh.

This will refresh every 5 seconds.

ActiveWorkbook.RefreshAll




alertTime = Now + TimeValue("00:00:05") 'hh:mm:ss
    Application.OnTime alertTime, "Refresh"

Re: Find empty textbox on userform

I think what you're wanting is something along these lines?

Private Sub CommandButton1_Click()




Cells.Find(What:=TextBox1.Value, After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate




TextBox1.Value = ""
TextBox1.Value = ActiveCell.Offset(1, 0).Value
End Sub

Re: Sorting Data in a column retaining labels.

The problem you have here is the merged cells. Unmerge them and then sort.

Re: Copy Text Box 88, Text Box 100, Text Box 104.Text Box 112 Value from LayOut Sheet

This would select the relevant boxes and copy them.

ActiveSheet.Shapes.Range(Array("TextBox 100", "TextBox 88", "TextBox 112", _
        "TextBox 104")).Select
    Selection.Copy

I'm not sure on the location to where you want them pasted.

Re: VBA code to automatically send email with specific attachment and include signatu

You can just add another .Attachments.Add "blah blah" for additional files.

As for your network mapping issue there are several ways you could deal with that.

The easiest would be to get everyone to standardize and use the same letter.
You could use an If statement to check if the drive exists and loop until found.

Or you could use this code to find the specific drive letter.
http://excelexperts.com/list-and-type-of-drives-vba

Hope that helps.

Re: VBA code to automatically send email with specific attachment and include signatu

Under ".body = Email_Body" add:

.Attachments.Add "Blah blah"

Re: Calculate difference between Date/Time in 2 columns excluding Holidays/Weekends

You'd have to setup a column with a list of the holiday dates. Excel doesn't know 'Holidays'

Re: run same code though all sheets

hmmm.... have you left the piece of code in that directs back to 'ppe' tab?