VB:I would welcome people's advice and suggestions on what to do. In my real project the range to search in for the VLOOKUP function can be 9000 rows long in some weeks and 900 in another. The same is true for all the content held on the various other sheets in that workbook, since their content is imported weekly. Ideally I'd like to be able to define at which row the range should end, instead of thinking to myself "Insert a really large number just to be sure that no content is missed out."ChineseYearsWhenPresidentsTookOffice() 'The number of rows that the lists of presidents and Chinese years occupy on their sheets vary over time 'so we need to set a range in each particular instance. 'There are headers in sheet "Presidents" so I start my range at A2 rather than A1. There are no headers in sheet "Chinese" so the range starts at A1. rowsPresidents = Sheets("Presidents").Range("a2").CurrentRegion.Rows.Count rowsChineseYears = Sheets("Chinese").Range("a1").CurrentRegion.Rows.Count Sheets("Presidents").Select 'If I wanted to change the contents of column A on sheet "Presidents" to display in italics I could use one of the following 'making use of rowsPresidents via concatenation to apply the command to that particular range, even though the number 'of rows won't necessarily be the same every time. 'With Range("a2:a$" & rowsPresidents) '.Font.Italic = True 'End With 'Range("a2:c$" & rowsPresidents).Select 'Selection.Font.Italic = True 'Here's where I'm stuck: 'Let's go to the list of presidents and apply a formula in column C in every row that a president is featured. 'Fortunately, we've already defined the number of rows and so can insert the final row number in the range via concatenation 'using the variable rowsPresidents. So: "ActiveSheet.Range("c2:c$" & rowsPresidents).Formula =" is the start and works fine. 'I would like to do the same in the VLOOKUP function, telling it to look in columns A and B of sheet "Chinese" 'from row 1 to the end of the pre-defined range using rowsChineseYears. ' I can't see how, though, so have to instead enter an arbitrary row number which I hope will cover all the range. ActiveSheet.Range("c2:c$" & rowsPresidents).Formula = _ "=VLOOKUP(a2,Chinese!a$2:b$400,2,FALSE)" End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:ok This returns the value in the formula with:ActiveSheet.UsedRange.Rows.Count - 4 LeaversYTD = "=IF(ISERROR(VLOOKUP(CONCATENATE(A" & i & "," & " " & " " & "," & "Total" & "),'[Leavers_Report.xls]YTD Summary LOS'!$B:$D,3,0))=TRUE,0,VLOOKUP(CONCATENATE(A" & i & "," & " " & "," & "Total" & "),'[Leavers_Report.xls]YTD Summary LOS'!$B:$D,3,0))""" Next i Cells(i,2).Value= LeaversYTDIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:What I want to do is have the functions performed by VBA, leaving only the value in the cell.Private Sub Submit_Click() Dim NewRow As Long Range("A7:A8").Select Selection.AutoFilter Selection.AutoFilter Field:=1 NewRow = ActiveSheet.Range("A812").End(xlUp).Row + 1 With ActiveSheet Range("A" & NewRow) = ComboBox1.Value & ComboBox2.Value & ComboBox3.Value & ComboBox5.Value & ComboBox4.Value UserForm1.Hide Range("B" & NewRow).FormulaR1C1 = "=Mid(RC[-1],10,5)" Range("C" & NewRow) = Application.WorksheetFunction.VLookup(Range("A" & NewRow), Sheets("Lookup").Range("N:Q"), 2, False) Range("D" & NewRow).FormulaR1C1 = "=Mid(RC[-3],7,3)" Range("E" & NewRow).FormulaR1C1 = "=Vlookup(RC[-4],Lookup!N:Q,3,False" Range("F" & NewRow).FormulaR1C1 = "=Mid(RC[-5],15,25)" End With ' Range("A7:A999").Select ' Selection.AutoFilter ' Selection.AutoFilter Field:=1, Criteria1:=Left(Range("A3"), 3) & "*", Operator:=xlAnd MsgBox "Please Re-Filter Rows", vbDefaultButton1, "SGA Template" End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Sub Update_Formulas_Data() Dim sPath1 As String Dim wb1 As Workbook Const sFileInp1 As String = "Formulas.xlsm" sPath1 = fPath & fDate & "_157Support_Summaries" Set wb1 = Workbooks.Open(sPath1 & sFileInp1) Range("AV1").Value = fPriorDate Range("AV1").NumberFormat = "dd-mmm-yyyy" End SubIt works. I need help creating a vlookup formula which will use that cell refernce. First I have to concatenate the cell reference with the suffix "_CDS", and I must do so while mainting the dating format, dd-mmm-yyyy. Thats is where I am currently stuck. After that is done, I believe I should be using the Indirect worksheet function, but I am not sure how that is done. Any help is greatly appreciated.
Function ConcatIf(ByVal compareRange As Range, ByVal xCriteria As Variant, Optional ByVal stringsRange As Range, _ Optional Delimiter As String, Optional NoDuplicates As Boolean) As String ' code base by Mike Rickson, MrExcel MVP ' used as exactly like COUNTIF() with two additional parameters ' of delimiter and "no duplicates" as TRUE/FALSE if concatenated values ' might include duplicates ex. =ConcatIf($A$1:$A$10,C1,$B$1:$B$10,", ",True) Dim i As Long, j As Long With compareRange.Parent Set compareRange = Application.Intersect(compareRange, Range(.UsedRange, .Range("a1"))) End With If compareRange Is Nothing Then Exit Function If stringsRange Is Nothing Then Set stringsRange = compareRange Set stringsRange = compareRange.Offset(stringsRange.Row - compareRange.Row, _ stringsRange.Column - compareRange.Column) For i = 1 To compareRange.Rows.Count For j = 1 To compareRange.Columns.Count If (Application.CountIf(compareRange.Cells(i, j), xCriteria) = 1) Then If InStr(ConcatIf, Delimiter & CStr(stringsRange.Cells(i, j))) <> 0 Imp Not (NoDuplicates) Then ConcatIf = ConcatIf & Delimiter & CStr(stringsRange.Cells(i, j)) End If End If Next j Next i ConcatIf = Mid(ConcatIf, Len(Delimiter) + 1) End Function
Function MultiVLookup(vMatchCriteria As Variant, rngLookUpArea As Range, lOffset As Long, Optional sDelimiter = ",") As String Dim rngMatchValue As Range Dim sFirstAddress As String Dim sTmpReturn As String sTmpReturn = "" With rngLookUpArea Set rngMatchValue = .Find(vMatchCriteria, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False) If Not rngMatchValue Is Nothing Then sFirstAddress = rngMatchValue.Address Do sTmpReturn = sTmpReturn & rngMatchValue.Offset(0, lOffset).Value & sDelimiter Set rngMatchValue = .FindNext(rngMatchValue) Loop Until rngMatchValue.Address = sFirstAddress End If End With If Len(sTmpReturn) > 0 And Len(sDelimiter) > 0 Then sTmpReturn = Left(sTmpReturn, Len(sTmpReturn) - Len(sDelimiter)) End If MultiVLookup = sTmpReturn End FunctionIf I call this from a VBA subroutine it works fine, and produces the expected results:
Sub TestCall() MsgBox MultiVLookup("A", ThisWorkbook.Sheets(1).Range("A1:A3"), 1) End SubIf, however, I call it as a UDF it fails. A quick bit of debugging shows that this is because the .FindNext method returns an empty range, which shouldn't be possible, as there are both more matches within the range and, even if there weren't, there must have been at least one match, so it should loop back to that.
And that does work as a UDF.
So, I guess my question not isn't so much "How do I make this work" as it is "Why the hecky-thump didn't it work with .FindNext". Anybody care to enlighten me?