Finding text in a cell and move contents 2 cells down...

[bodo62]

Hello all.

Here is the problem:

There is an excel file to which we import 2 tables from a webbsite.

The last column in the tables look something like this:
A B C D E
1 AVERAGE
2 DATE TIME NAME POINTS
3 15/3/05 09.15 John 500 250

A B C D E
300 AVERAGE
301 DATE TIME NAME POINTS
302 13/3/05 09.25 John 400 250


As you can see last column by some reason starts one row earlier than the others. I need to find the cells containing "AVERAGE" (E1 and E300) and move them to the cell under it (E2 and E301). The cell containing "AVERAGE" will always be in column E, but can be in different rows. How can I automate this with VBA code.

Thanks in advance.

Regards,

Bodo.

 

[Shippwreck]

Something like this perhaps

Sub move()
move_cell = range ("e1").value
range ("e2").value = move_cell
End Sub

hope this helps

http://www.orlandoluxuryvilla-pool.com

 

[Shippwreck]

also to delete the row you no longer want try adding

Rows("1:1").Delete Shift:=xlUp

at the end of the macro

http://www.orlandoluxuryvilla-pool.com

 

[bodo62]

Yes, that might be a partial solution, however I do not always know in which cell the word "AVERAGE" will be, since the second table starts after the first one, but I don't know on which row. What I really need is to find which cell contains "AVERAGE" and then move the found cell to next row.

 

[OlafBogus]

try something along the lines of

Columns("E:E").Select
    Do
        Selection.Find(What:="ACCESS", After:=ActiveCell, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
            MatchCase:=False).Activate
        ActiveCell.Offset(-1, 0).Value = ActiveCell.Text
ActiveCell.Value = ""
        Selection.FindNext(After:=ActiveCell).Activate

    Loop

 

[SkipVought]

How are you IMPORTING these files?

If you were to use the Data/Get External Data/Import Text... then you could control this process by starting the import at ROW 3 for the second file. Then this screwy heading burried in your table, would not be an issue.

Skip,
[sub]
[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart! [/sub]

 

[bodo62]

Hi Skip

I am importing the data from a Webbpage through Data/Get External Data/Import/ but all the information is on a single table follow link to see what tables I mean

http://www.stdf.org/serie/SERIE/outstand/2C.htm

 

[Shippwreck]

Hi,

Ah didn't realise that, try this then:

Sub Macro()
Range("a1").Select
For mynum = 1 To 2
Cells.Find(What:="average", After:=ActiveCell).Activate
ActiveCell.Offset(1, 0).Value = "average"
ActiveCell.Offset(1, 0).Activate
Next
End Sub

Hope this helps

http://www.orlandoluxuryvilla-pool.com

 

[bodo62]

Thanks Shippwreck, this will put me into track again...

 

[Shippwreck]

Just glad to help, i know i have been helped a lot on this forum

Matt

http://www.orlandoluxuryvilla-pool.com