finds and concatenate

  • Thread starter Thread starter vikram
  • Start date Start date
V

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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
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
 
Back
Top