Posts by bob22

The formula seem separated to me. What is it you are trying to accomplish? I'm missing something.

Seems some of your question is missing after: "After having populated it, I can..?"
Perhaps send the macro/file?

I'll do it automatically for $5 a day.
Seriously, google importing data bloomberg vba excel and you'll get work done to start with. They come back here and ask for some help after you've given it a go yourself.

Re: Mathematical algorithm to get an inverse of an RBG color

Why not use something like:

[COLOR=#666666][FONT=&quot]Range("A1").Interior.Color = RGB(255, 0, 0)[/FONT][/COLOR]

where the color code could vary depending on the value of cell's content.
Conditional formatting would work and a vba could also do it.

Re: Need to copy row from input box answer, but unable to do so. Facing difficulties!

I did a bit of revising; not sure what you are trying to do.
This code looks at Column A and selects a range using your Input box and then pastes it in row 12.

Sub test()
Dim RowCount, ChooseRow As Integer
Dim xpproduction As Sheets
Dim copyRange As Range
 
Worksheets("xpproduction").Activate
RowCount = Cells(Rows.Count, "A").End(xlUp).Row
 
If RowCount > 1 Then
    ChooseRow = InputBox("please select the row you want")
     
    Set copyRange = ActiveSheet.Range("A2:A" & ChooseRow + 1) 'ERROR LINE
    copyRange.SpecialCells(xlCellTypeVisible).Copy
    
    With Sheets("xpproduction").Cells(12, 1)
       .PasteSpecial xlPasteValues
    End With
    
End If


End Sub

Re: VBA: Result only one sentence in each row of column B.

I think you may be better off at a VBA teaching code site. I believe this site is to assist members with fixing/improving the code they've written, not to do it for them.
If I'm wrong, someone may be along to correct me.

Re: VBA: Result only one sentence in each row of column B.

I assume you are asking someone to develop the macro for you?

Re: VBA: Result only one sentence in each row of column B.

Need to ask your question a bit more fully.

Re: Copy rows of data to another worksheet if a condition is met in one of the cells

Try the attached.
forum.ozgrid.com/index.php?attachment/72682/

Re: VBA Volitale function controlling rowheight

I'm not understanding.
What are you trying to do? What range does cells(target.row,8) refer to?

What does:
Formula is placed continuously in column H is as follows.
=IF(N7=0,"decrease","expand")
mean? Is it the same N7 for each cell or does it change by row, e.g., N7, N8, N9...

Re: Automatically populate cells with selection from drop down list

I think you may need a vendor more than some assistance but that's just a first impression.
Do you have a sample workbook that shows your efforts based on your research as a starting point for others to help?

Re: Concatenate range of column values in a row

I found a solution using a function:

Function ConcatenateRange2(ByVal cell_range As Range, _                    Optional ByVal seperator As String = ",") As String




Dim cell As Range
Dim newString As String
Dim cellArray As Variant
Dim i As Long, j As Long




cellArray = cell_range.Value








For i = 1 To UBound(cellArray, 1)
    For j = 1 To UBound(cellArray, 2)
        If Len(cellArray(i, j)) <> 0 Then
            newString = newString & (seperator & cellArray(i, j))
        End If
    Next
Next




If Len(newString) <> 0 Then
    newString = Right$(newString, (Len(newString) - Len(seperator)))
End If




ConcatenateRange = newString




End Function

Re: Concatenate range of column values in a row

2013; I know 2016 has the concat function.

I've 144 or so columns of potential data that I want to concatenate in the attached file.
forum.ozgrid.com/index.php?attachment/72293/

Some rows have only 1 item to concatenate but others have up to 17.

I'd like to avoid having to write out:
=concatenate(b2,",",b3,",",b4,",", etc..... to eq2) as a formula.

Most bits I found on line use concatenate with a transpose but I'm not transposing data.

I tried =CONCATENATE((B2:EQ2)&",") but got a #Value.

Thanks for any help!

Re: Match relevant data again

I appreciate it.
I use sumifs frequently and got hung up on index/match. Forest for the trees.

Re: Match relevant data again

Mea culpa!
I apologize for causing you to do needless work on my behalf and do appreciate your most recent assistance.

Re: Match relevant data again

Ali,

It works but only for a single age. I need to sum up a number of ages, say from 60-80 years of age.

I'm working on cell L11269 for ages 83 to 84 to keep it simple in this file: [ATTACH=CONFIG]72186[/ATTACH]

I tried to also use a NamedRange in the formula (for UK: uk_sexes which refers to a helper cell that shows the NamedRange to select the population data from to have it select the sums from the desired column of data, e.g., Both Sexes, Male Only, Female Only, but it did not work:
=INDEX($G$2:$G$11251,(MATCH(1,($D$2:$D$11251=D11268)*($C$2:$C$11251=L11268),0)))
changed to:
=INDEX(uk_sexes,(MATCH(1,($D$2:$D$11251=D11268)*($C$2:$C$11251=L11268),0)))

uk_sexes is cell F11268 and the cell value is both_pop as I want the formula to capture pop data for both sexes.
both_pop is the named range for the Both Sexes Population F2:F11251.

Additional help is appreciated!

Re: Match relevant data again

Thanks for the explanation; I'll look at the Evaluate function as you suggest.

Re: Match relevant data again

Thank you again!
I don't immediately grasp this part:
(MATCH(1,($D$2:$D$11251=D11268)*($C$2:$C$11251=L11268),0)
and if the * is indicating multiplication (I don't think so).
I have to run out; will review and consider the formula some more.

Re: Match relevant data again

I am working on the UK value formula for L11269 as my example in revised attached:
file: forum.ozgrid.com/index.php?attachment/72185/

Help if you can.