finds and concatenate

[vikram]

Hi all,

I have a situation where in cell A1 , there is a date .
what i want is that a macro which upon finding a date format in colum
A,picks up cells below that date column and which would be A2 and als
picks B2 and combines them (like concatenate) and inserts a colum
before column B and puts them adjacent to the date column.

like if we have a date column as cell A1, what this macro shud do is i
cell B1 there shud be a concatenate of A2 & B2.

can u guys help
thank u so muc

 

[Frank Kabel]

Hi
why not use a formula. e.g. the following in C1:
=IF(ISNUMBER(A1),A2 & B2,"")

 

[vikram]

the problem is that in column A there would be dates in various rows
like in A2,A8,A70,80 and so on and there are around 50 such rows

 

[Frank Kabel]

Hi
just copy this formula down for all rows

 

[vikram]

ok, take a look at the attached excel file, a small file...and look i
column A and the dates part


thank

Attachment filename: 8213 123001 account analysis - subledger d_060504.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=54982

 

[Frank Kabel]

Hi
o.k. I see
this looks like an imported file from a backend application...
To be honest I would try to preprocess this imported file

 

[vikram]

ok, i have attached the text file if u wnt to chec

 

[vikram]

Please help me with thi

 

[DNF Karran]

I have to say- I hate these kinds of files, why do people make report
like this???

Anyway, rant over, which ranges are useful to you? is there a colum
containing blank cells (or other info) that you can remove the row for
Are there any ways of identifying the useful ranges (text string etc)?

Dunca

 

[vikram]

in the attached excel file, which is a small excel file, i need t
concatenate wht i hve written in the first post of mine, to find dat
format and pick up the cells below it and concatenate them afte
inserting a column before column B

if u see that excel file , u will find that below some of the date
there are customer names, what i want is that tjose customer names shu
come in front of those dates.

thank u so much for ur hel

 

[DNF Karran]

Without knowing more about the report etc the attached is all I can do.

The code cycles though col A looking to see if the numberforma
property is "d-mmm-yy". If it is does the transformation you aske
for.

Dunca

Attachment filename: 8213 123001 account analysis - subledger d_060504.xl
Download attachment: http://www.excelforum.com/attachment.php?postid=55099

 

[vikram]

hi can u please send me the code, i am not ble to download the file!

thank u so muc

 

[DNF Karran]

Here ya go:

Option Explicit

Sub MatchCells()

Dim loCurRow As Long

For loCurRow = 1 To ActiveCell.SpecialCells(xlLastCell).Row
If Range("A" & loCurRow).NumberFormat = "d-mmm-yy" Then
Range("B" & loCurRow).Value = Range("A" & loCurRow
1).Value & Range("C" & loCurRow + 1).Value
End If
Next
End Su

 

[vikram]

Hi do we have a macro which deletes all the text in a column.

suppose I have column A and in that I have date formts and text, i wn
that dates remain there but text does not

thanks again for ur kind hel

 

[DNF Karran]

you could edit the code I gave you to get:

Sub MatchCells()

Dim loCurRow As Long

For loCurRow = 1 To ActiveCell.SpecialCells(xlLastCell).Row
If Range("A" & loCurRow).NumberFormat <> "d-mmm-yy" Then
range("a" & locurrow).clearcontents
End If
Next
End Su

 

[vikram]

thank u so much karren

worked perfectly
thanks again
i really appreciate ur hel

 

[vikram]

THANK U SO MUCH KARREN
i really appreciate ur help
thanks again but i need a favor from u again

can we have a macro which works like this

suppose i have column B where I hve some text data and I have column
where also i have some text data

what i want is that the data from column C should move to column B bu
if some cells in column C are blank it should not replace any dat
which is there in column B

thanks agai

 

[DNF Karran]

If the only condition is C is not empty then:

Sub MatchCells()

Dim loCurRow As Long

For loCurRow = 1 To ActiveCell.SpecialCells(xlLastCell).Row
If Range("C" & loCurRow).NumberFormat <> Empty Then
range("B" & locurrow).value = range("C" & locurrow).value
End If
Next
End Sub


Dunca

 

[vikram]

Hi karen,

It is replacing cells in column B with the blank cells in columnC

please hel

 

[DNF Karran]

OOPS, sounds bad- helps if i delete the .numberformat property whe
looking at the cell value!!!

Sub MatchCells()

Dim loCurRow As Long

For loCurRow = 1 To ActiveCell.SpecialCells(xlLastCell).Row
If Range("C" & loCurRow) <> Empty Then
range("B" & locurrow).value = range("C" & locurrow).value
End If
Next
End Su