Posts by LynChen

=MID(CELL("filename",A25),SEARCH("[",CELL("filename",A25))+1,SEARCH(".",CELL("filename",A25))-1-SEARCH("[",CELL("filename",A25))) & "-" & RIGHT(CELL("filename",$A$1),LEN(CELL("filename",$A$1))-FIND("]",CELL("filename",$A$1),1)) & "-" & TEXT($A25,"dd") & "-" & TEXT(ROW($C25)-1,"000")

this code allow me to write the row data in column C with this format:
================================
file name + sheet name + column A + ###
================================
it works mostly what i wanted, but there are 2 things i love to get better and need helps from you all.

lets say I drag this code from C2-C20

1. I have fill in on A1 & B1, and i need extra 4 rows to write details for A1 & B1 row,
and I start a new row from A6, (so i have no data in A2, A3, A4, A5, nor B2, B3, B4, B5)
I like to have C1 last 3 digital ### as 001 and C6 last 3 digital ### as 002
(right now it will be 005)

2. Similar concept when I delete data C13-C18, with column A1, A6-A20 fill-in with data,
(i did not delete the whole row, still have A13-A20 data filled-in as it is),
in C19, the last 3 digit ### will be 018, but I like it to be 009 (the reason it should be 009 is that C6 should be 002,
C7---003, C8---004, C9---005, C10---006, C11---007, C12---008, C13-C18 deleted, so it is blank, and C19---009

can any one help me to change the original code and make it works the way I wish it to be?
(Ps. if possible it will be great just stick with formula without using VBA, thanks!)

extra thing:

is there anyway to also put change row font colour in certain places? for example i like to have A2 as black and then A3-A5 as white (kind of to hide it with font colour), and A6 as black colour, any fomular i can use to make it black or white?

So you can say that if A2, A3, A4, A5, is the same data (eg, New York), I like to keep first data A2 as black and show New York, and the rest as white; and A6, A7, A8, A9, till A20 is same data (eg, LA), I like to keep A6 as black and show LA, and A7-A20 is white.

Re: Find Value And Return Row Number

Quote from Trebor76;418783

stickyfeet,

Though Dave has beaten me to the punch (and I'd say his solution is the way to go), here's my attempt nonetheless:

Code

Sub Macro1()


Dim intMyVal As Integer
Dim lngLastRow As Long
Dim strRowNoList As String


intMyVal = 1 'Value to search for, change as required.
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row 'Search Column A, change as required.


For Each cell In Range("A2:A" & lngLastRow) 'Starting cell is A2, change as required.


    If cell.Value = intMyVal Then
        If strRowNoList = "" Then
        strRowNoList = strRowNoList & cell.Row
        Else
        strRowNoList = strRowNoList & ", " & cell.Row
        End If
    End If
Next cell


MsgBox strRowNoList


End Sub

Display More

Regards,

Robert

Display More

Hi Robert,

This is a great code, i am very new to vba, i need help about the output.
Right now the out put is a string with comma, how can i put print them as array or into cells, like A1,B1,C1,D1..., or A1, A2, A3, A4... many thanks in advance.