Re: VLOOKUP partial cell data
You can use a countif.
e.g
=CountIf(A:A,"MVPN*")
If you want to sum up another column based on the values in Column A you can do a sumif
e.g (Assuming the values you want to sum up are in column B)
=SUMIF(A:A,"MVPN*",B:B)
Re: lookup the minumum value in one column based on value in another.
I tried an Array formula, must have done something wrong with it because this one works fine.
Thanks a bunch!
Re: new record on new row line?
I'm assuming you have are entering things into C4, c13, c22, c24, c26 and c27 to create a record and then want the records stored as lines of data?
If this is correct I suggest you have one sheet for data entry and a second sheet for the lines of data.
If you permanently have e4:j4 with the formula you put in the above code and then add the below code it will copy the line of data and create a new record on sheet 2
(where sheet 1 and sheet 2 please change to whatever you want to call the sheets)
Sub AddRecord()
Application.ScreenUpdating = False
Sheets("Sheet2").Select
Rows("4:4").Select
Selection.Insert Shift:=xlDown
Sheets("Sheet1").Select
Range("e4:J4").Select
Selection.Copy
Sheets("Sheet2").Select
Range("A4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A4").Select
Sheets("Sheet1").Select
Range("c4,c13,c22,c24,c26,c27").Select
Selection.ClearContents
Range("C4").Select
Application.ScreenUpdating = True
End SubI am currently trying to find the first time a record appears in a large set of data.
What I want, is to lookup a reference number that may appear multiple times in a column (A) and return the minimum value in column (B) based on the reference number in Column (C).
So in column D I would have a formula that looks up the value in column C, matches it in column A and returns the minimum value in column B.
E.g
A B C D
X 2 X 2
Y 1 Y 1
X 3 X 2
X 4 X 2
Z 1 X 1
Any ideas?
Thanks in advance!
Re: Macro working on one system but not another - only difference is sp2 vs sp3
Thanks, I have managed to fix it.
I disabled the error checking by putting On Error Resume Next before the code and found that the macro was doing everything correctly but their system didn't seem to be keeping the copied data in memory. So I added another line of code to copy the data just before pasting it, rather than before opening the file it gets pasted in and it now works a charm!
I still find it strange that it worked for hundreds of other people and just this one person had a problem!
Re: Macro working on one system but not another - only difference is sp2 vs sp3
It still isn't working, the same line of code just brings up the same error...but only in Italy!
Re: Macro working on one system but not another - only difference is sp2 vs sp3
Thanks, I tried this and it didn't work. However I noticed while doing this that I was defining the Row before opening the sheet (which worked anyway for everyone else using this file), so I have altered this and sent it to my colleague in Italy to test, I'll likely find out on Monday if it's worked.
Re: Macro working on one system but not another - only difference is sp2 vs sp3
Also, please note the line of code highlited is actually the paste special - values line.
Re: Macro working on one system but not another - only difference is sp2 vs sp3
The file opens that the data should be copied into, however they get a run-time error 1004 and the line higlighted in red above is highlighted in the VBA screen.
I work with several countries so I don't think the language settings are the problem. What do you mean by decimal separators? The document has validation on all of the cells that they can edit and it is protected when they run this macro (obviously it gets unprotected within the macro).
Hello,
Any help with this will be appreciated! I am getting a bit stressed over it.
The below code is working on my system, as well as hundreds of other systems around my business.
However, there is one place it isn't working, (Italy) and the only difference is that their version of excel is Service Pack 3 and mine is Service Pack 2. I have checked Add-Ins etc and they all look the same...
Any ideas?
Thanks!
Sub Copy_to_LCCLog()
'Copies all of the information from the LCCTemplate into the IntLog.xls
'File path is FN
Dim FN As String
Dim wb1 As Workbook
Set wb1 = ActiveWorkbook
Dim Row As String
FN = Range("db17")
Sheets("Template LCC").Select
ActiveSheet.Unprotect
ActiveSheet.Shapes("Rectangle 31").Visible = False
ActiveSheet.Shapes("Rectangle 56").Visible = True
ActiveSheet.Shapes("Rectangle 30").Visible = True
Range("y6").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.ScreenUpdating = False
Row = 2
Row_dub = 0
Sheets("Customer Log").Select
Calculate
Range("A10").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
TimeID = Cells(10, 1).Value
Range("A13:AT13").Select
Selection.Copy
Workbooks.Open Filename:=FN
Worksheets("Customer_Log").Select
Range("a1").Select
Do While Cells(Row, 1) <> ""
If Cells(Row, 1) = TimeID Then Row_dup = Row
Row = Row + 1
Loop
If Row_dup > 0 Then Row = Row_dup
[COLOR=black]Cells(Row, 1).Select[/COLOR]
[COLOR=red]Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _[/COLOR]
[COLOR=red]:=False, Transpose:=False[/COLOR]
'add Barcorp Log
Windows("IntLog.xls").Activate
Sheets("BarcorpCont_Log").Select
Row = 2
Do While Cells(Row, 1) <> ""
If Cells(Row, 1) = TimeID Then
Range(Cells(Row, 1), Cells(Row, 14)).Select
Selection.Delete Shift:=xlUp
Row = Row - 1
End If
Row = Row + 1
Loop
Endrow = Row
ThisWorkbook.Activate
Sheets("BarcorpCont Log").Select
Row = 4
Do While Cells(Row, 3) <> ""
Row = Row + 1
Loop
Range("A15:P19").Select
Selection.Copy
Windows("IntLog.xls").Activate
Sheets("BarcorpCont_log").Select
If Row > 4 Then
Cells(Endrow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
'add Facility
Windows("IntLog.xls").Activate
Sheets("FacilityDetails_Log").Select
Row = 2
Do While Cells(Row, 1) <> ""
If Cells(Row, 1) = TimeID Then
Range(Cells(Row, 1), Cells(Row, 14)).Select
Selection.Delete Shift:=xlUp
Row = Row - 1
End If
Row = Row + 1
Loop
Endrow = Row
ThisWorkbook.Activate
Sheets("Facility Details").Select
Row = 4
Do While Cells(Row, 3) <> ""
Row = Row + 1
Loop
Range("A15:AN22").Select
Selection.Copy
Windows("IntLog.xls").Activate
Sheets("FacilityDetails_log").Select
If Row > 4 Then
Cells(Endrow, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End If
ThisWorkbook.Activate
Sheets("Customer Log").Select
Range("A10").Select
ActiveCell.FormulaR1C1 = "='Template LCC'!R[-4]C[24]"
Windows("IntLog.xls").Activate
Selection.AutoFilter Field:=1, Criteria1:="0"
Rows("2").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Selection.Delete Shift:=xlUp
Range("A1").Select
Selection.AutoFilter Field:=1
ActiveWorkbook.Save
ActiveWorkbook.Close
ThisWorkbook.Activate
Sheets("Template LCC").Select
Range("y6").Select
Selection.Copy
Range("CW1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("E6").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
, AllowFormattingColumns:=True, AllowFormattingRows:=True, _
AllowInsertingRows:=True, AllowDeletingRows:=True
End Sub