Posts by JonathanVH

If your above range is in A1:C4 with the column descriptions in row 1, then the formula in E2 would be:

=VLOOKUP($D2,$A$2:$C$4,3)

You can then copy that down column E.

No, there are better ways--I was going to suggest using the Areas property. The two questions, though, are:

• Do the ranges contain actual blank rows, or are the "blank" rows always actually formulae?
• Does every range have a one-row header?

If there's some way to identify what rows are "headers," e.g., the first row in any block of cells, and there are no actual blanks (i.e., any cells that look blank actually have formulas) in your ranges, then you could have your routine adjust automatically if you add more ranges (or rows to ranges). Is that the case here?

And it will, as their names do not start with the word "Sheet." That's what "Like Sheet*" is looking for; the asterisk is a wildcard.

Use a VLOOKUP with the entire range as the table_array and 3 as the col_index_num.

Good catch. I missed that in my analysis.

Again, i assume that unnamed sheets are those that have been created with the default names (Sheet1, Sheet2, etc.) and your P2 cells do not have names like that:

For Each ws In Worksheets
  If ws.Name Like "Sheet*" Then
    On Error Resume Next
    If Len(ws.Range("P2")) > 0 Then
      ws.Name = Replace(ws.Range("P2").Value, "/", "-")
    End If
    On Error GoTo 0
    If ws.Name <> Replace(ws.Range("P2").Value, "/", "-") Then
      MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
    End If
  End if
Next

Cell B14 (with the dropdown list) uses range D4:D6 for its validation list.The adjacent cells (which need not be adjacent) have formulas to lookup from the neighbours range. Cell C14 has
=VLOOKUP($B$14,$D$4:$F$6,2,FALSE)
and cell D14 has
=VLOOKUP($B$14,$D$4:$F$6,3,FALSE)
The cells below those look up the populations. Cell C15 has
=VLOOKUP(C$14,$A$3:$B$7,2,FALSE)
and cell D15 has
=VLOOKUP(D$14,$A$3:$B$7,2,FALSE)
The graph uses range C14:D14 of course. One could have its title include the value in C14.

I should have time later today to create an example of using array formulas to find the cities closest in population. Or perhaps you could work it out with that clue.

I just analyzed your code for logic rather than just syntax (which was causing the errors) and it doesn't compute. I doubt you want myRange to loop until the last row. If you're just looking to add names where both conditions are true, you don't need (or want) an inner loop: just check what's in the row's A column and add the name if it's an X; e.g.:

For Each rngCommercial In sh3.Range("C1:C" & lastrow)
    If (rngCommercial = "Commercial" Or "SalesOps") And rngCommercial(,-1) = "x" Then
      strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & "; "
    End If
  Next rngCommercial

Now you don't have the inner If condition closed. You can exit the For loop when you find your value, but the loop needs to close (and return to the For clause) for any previous values (even if there are none).
I encourage you to format your code by indenting to see where anything opens and closes, e.g.:

Dim lastrow As Long
Dim myRange As Range 'Column A - needs an x
Dim rngCommercial As Range 'Column C - function needs to be match


  lastrow = sh3.Cells(Rows.Count, 1).End(xlUp).Row
  For Each rngCommercial In sh3.Range("C1:C" & lastrow)
    If rngCommercial = "Commercial" Or "SalesOps" Then
      For Each myRange In sh3.Range("A1:A" & lastrow)
        If myRange = "x" Then
          strCommercial = strCommercial & rngCommercial.Offset(0, -1).Value & "; "
          Exit For
        End If
      Next myRange
    End If
  Next rngCommercial

You're missing the Next for your inner loop. You have two Fors, so you need two matching Nexts.

I did this by using a dropdown list validation and a couple of lookup ranges:
[ATTACH=JSON]{"data-align":"none","data-size":"full","title":"Cities.JPG","data-attachmentid":1207344}[/ATTACH]

It's unclear to me how cities (city names?) would appear as a "chart." A chart displays numerical comparisons, and "cities" are not that. If you want to show how distant (or how populous, or how large in hectares, etc.) each neighbouring city is, then that could be a chart.

You'll have to define "similar" for yourself, but I would take the colors as hex values and then compare the three pairs of bytes, e,g,.:

Sub CompareColors()
Dim p As Long, RGB As Variant
  RGB = Array("RED", "BLUE", "GREEN")
  With ActiveCell
    For p = 1 To 6 Step 2
      Debug.Print RGB(p \ 2), Abs(Val("&H" & Mid$(Right$("000000" & Hex(.Interior.Color), 6), p, 2)) - Val("&H" & Mid$(Right$("000000" & Hex(.Font.Color), 6), p, 2)))
    Next
  End With
End Sub

If you mean sheets assigned default names and you don't have values starting with "Sheet" in the range, I guess you could wrap the code in an If statement like this:

If ws.Name Like "Sheet*" Then

Assuming you're using Outlook, have a look at Ron's first example on this page. For the strBody, concatenate your message from the range of cells.

Put something like this in the Total Price cell:

=IF((L5+S5+AA5<>1)+(L7+S7+AA7<>1),"You must order one polo and one jacket",IF((L5<>L7)+(S5<>S7)+(AA5<>AA7),"Mixed types not allowed",(L5+L7+S5+S7+AA5+AA7)*C5))

I would turn the range into a table and then show the users how to use the dropdown filter on the column header.

How about something like this:

=INDIRECT("'INSERT TAG NO (" & 2 & ")'!S2")

You can do that with some VBA code ("macro"). I'd use something like this:

Application.Wait (Now() + TimeSerial(0, 0, 1))

to update it every second.