MS Excel / VBA help thread

Nah I'm using SUMIFS and it needs to scale. Ended up re-writing some of the SQL to change the structure a little. Cost me about half a day's work though.

Excel 2010 Conditional formatting..
I'm having a mind blank.
Can I Use this to highlight a whole row rather than just the cell containing value required?

Yes, conditional formatting works best over a range

The part I'm blanking on is how to set it to highlight the row rather than a single cell.
A2 has the text but I can only highlight that automatically rather than Row A etc

It doesn't really work like that. It is designed to illustrate the distribution of data across a range.

I stand corrected!

Thanks guys,
Ended up doing it but cell, sort by cell colour , fill repeat.
But in the future I'll try your way Tiswas as this is going to be a new regular report.

Ive got some rows of data, most of which have information in. For each row I want to create a list of all the cells that are blank (ie their column header). I have managed to find the position of the first blank cell using [FONT=Arial][SIZE=2]=MATCH(TRUE,INDEX(V3:CA3="",0),0) but not sure how to make this find each other cell after that and make a list of it.
[/SIZE][/FONT]

This will be quite cumbersome to achieve in Excel, as you will need to explicitly check each cell in turn and append its location to a string. However, it's pretty straightforward with a few lines of VBA.

This (pretty poor) code makes a list in column A of each blank cell (by column number) in the row (from col B to col U) for rows 1-28. Should be pretty easy to modify

[code]

Sub thingy()

For i = 1 To 28
myStr = ""
For Each c In Range("B" & i & ":U" & i)

If c = "" Then myStr = myStr & ", " & c.Column

Next
Range("A" & i).Value = myStr
Next i

End Sub
[/code]

This will be quite cumbersome to achieve in Excel, as you will need to explicitly check each cell in turn and append its location to a string. However, it's pretty straightforward with a few lines of VBA.

This (pretty poor) code makes a list in column A of each blank cell (by column number) in the row (from col B to col U) for rows 1-28. Should be pretty easy to modify

[code]

Sub thingy()

For i = 1 To 28
myStr = ""
For Each c In Range("B" & i & ":U" & i)

If c = "" Then myStr = myStr & ", " & c.Column

Next
Range("A" & i).Value = myStr
Next i

End Sub
[/code]

Cheers, I think this is exactly what I want.

Alright im fairly new to VB, ive got the code changed to report column title, but can I get it to repeat untill the last cell, rather than enter the i min/max values?

Also, ive got a list of individual names from a larger set of data, how can I get Excel to produce lists of all data from the larger data set grouped under the individual names?

I basically want to do a VLOOKUP that returns multiple values relating to it

I don't fully understand your last problem, but iI understand that you're asking for vlookup to return a number of cloumns? so in the following table:

   age height weight

chris 23 175 10
simon 24 180 10.5
tim 25 185 11

you'd want a 'vlookup' of simon to return 24,180,10.5

Is that correct? In that case, you may be better off adding another column to the table which is a concatenate of all the previous ones and asking for that from the vlookup.

Your data then becomes:

     age height weight return value

chris 23 175 10 23,175,10
simon 24 180 10.5 24,180,10.5
tim 25 185 11 25,185,11

Does that make sense? You may need to write a little function to concatenate the data (i.e. produce the 'return value' column) to your own specification, and Mattty's code above looks like a sensible place to start for that

.

edit: sorted

[code]
Sub OperatorProp()

For i = 3 To 500

For j = 3 To 1727
    If Cells(j, "C") = Cells(i, "H") Then
     myStr = myStr & Cells(j, "B") & ", "
     End If

Next j
Range("I" & i).Value = myStr
myStr = ""
Next i

End Sub
[/code]

But is there a way to get the code to keep going until the end of the list without entering in the first and last value of the for loop?

ok, try this:

Public Function getAgeGroupList(age As Long, table As Range) As String
Dim i As Variant
Dim tableVar As Variant

Dim returnValue As String
Dim delimiter As String

delimiter = " | "
tableVar = table
For i = 1 To UBound(tableVar)
If (tableVar(i, 2) = age) Then
returnValue = returnValue & tableVar(i, 1) & delimiter
End If
Next i

getAgeGroupList = returnValue
End Function

give it a range of name | age columns (as your example) and a number of the age:

eg:

age group
20 Tom | Harry | Simon |
21 Dick | Chris |
22
23 Tim |

where the group column holds

=getAgeGrouplist(F9, B2:C7)

(B2:C7 holds your name | age data, F9 has the number 20, 21, 22 or 23)

Hope that makes sense

How can I get Visual Basic to enter:

Tube: Bayswater

Into a cell? I think its having problems with the characters, ive used " " on each side but its still giving an error.

[code]
range("a1").value = "Tube: Bayswater

should work
it enters it in cell A1 on the worksheet currently active

Ok, what about this?

mystr = mystr & "<a href="http://www.ncp.co.uk/find-a-car-park.htm?t=link&q=""

It seems to be having issues with the "" inside another ""

Yes, the syntax is to use double " when they appear in a string, so:

[code]
mystr = mystr & "<a href=""http://www.ncp.co.uk/find-a-car-park.htm?t=link&q="""
[/code]

Morning.

Yesterday I wrote a simple formatting macro and then was happily able to copy it and edit it to make new versions (for different filtering/sorting) for different tabs in the same workbook.

This morning I've gone to do the same but now can't edit or copy etc - all the buttons are greyed out apart from 'run' and 'stepinto'.

Why, oh, why???

I need to use the same macro for a number of tabs, but with slightly different filters, so don't really want to re-write it each time.

Any ideas?

Sounds like you've not enabled the macro content when you opened the file? Go to security centre and sort it bro.

aha.

I had enabled it all, but hadn't closed then re-opened the sheet.

All is now well in the world.
Ta!

Excel experts, I need some help.

I have a column (let's call it A) and in that column not all the rows have values. Is there a formula that will automatically put a value in the adjacent column (B) whenever there is a value (any value) in A? Like a full stop or comma for instance.

Data merges. Do not want.

Tiswas, that is exactly what I needed. Thank you! Blowing kisses your direction right now.