Sleeper: Simple (but hard for me) excel problem..columns

[Immatoity]

Hi,

Me again, I have a simple problem, but one I cannot work out how to resolve?
I have an excel sheet which is produced as a result of a macro already working?.(it copies and paste values pivot data)
The data looks like the below..(in attached bitmap)

What I need to do sounds easy? I want to move the column "Total Sum of TOTALVAL" to the column left of the one headed "Sum of Qty"?however, depending on what period this is all done, there could be 5 weeks, not the 4 above..which
means I can' t simply tell it to cut column X and paste it into X every time, as each time it might be different?
Any ideas?

PS I also need to get rid of decimal places in any "Qty fields"..again they might change every time depending on 4/5 weeker.

[mark007]

Make use of the find function e.g. using a function like this:

Function FindCol(strFind) As Integer
Dim R As Range
Set R = Sheet1.Cells.Find(strFind)
If Not R Is Nothing Then
FindCol = R.Column
End If
End Function

You could call it using:

MyCol=FindCol("Total Sum of TOTALVAL")

for example.

[Bob Phillips]

Sub MoveColumn()
Dim iPos As Long
On Error Resume Next
        iPos = Application.Match("Sum of QTY", Rows(1), 0)
    On Error GoTo 0
    If iPos > 0 Then
        Columns(2).Cut
        Columns(iPos).Insert
    End If
End Sub

[gibbo1715]

Function FindCol(strFind) As Integer

Dim R As Range
Set R = Sheet1.Cells.Find(strFind)
If Not R Is Nothing Then
FindCol = R.Column
End If
End Function

Mark

Im really interested in this short function, i am on a mission to shorten the code im my projects at the moment and considered a short search function as below

Function Find(strFind) As Integer
Cells.Find(What:=strFind, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
        .Activate
End Function

Then call it as follows

Sub Test()
Dim SearchFor As String
On Error GoTo Err
SearchFor = Find(InputBox("What do you want to search for", "Search"))
MsgBox ActiveCell.Value
Exit Sub
Err:
MsgBox ("Sorry Not Found")
End Sub

Seems to work OK or am i making a glarring error using this method?

Cheers

Gibbo

[Immatoity]

ermmm... a bit confused here.... xld I dont think yours works (unless I am doing something wrong)...

also I have no idea how to call a function in excel so I cannot test mark007's code...

also, does gibbo's have anything to do with mine( not being funny...just ignoring the code I need to if that makes sense)

[Bob Phillips]

ermmm... a bit confused here.... xld I dont think yours works (unless I am doing something wrong)...

A bit more detail would help us here. It worked fine for me, so in what way does it not work for you?

also, does gibbo's have anything to do with mine( not being funny...just ignoring the code I need to if that makes sense)

Not directly, but he is just looking to use the technique elsewhere.

[Bob Phillips]

Im really interested in this short function, i am on a mission to shorten the code im my projects at the moment and considered a short search function as below

Function Find(strFind) As Integer
Cells.Find(What:=strFind, After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False) _
        .Activate
End Function

Then call it as follows

Sub Test()
Dim SearchFor As String
On Error GoTo Err
SearchFor = Find(InputBox("What do you want to search for", "Search"))
MsgBox ActiveCell.Value
Exit Sub
Err:
MsgBox ("Sorry Not Found")
End Sub

Seems to work OK or am i making a glarring error using this method?

What you are doing is to create a generic find function, so a few suggestions as to how I would do it:

- don't call it Find, not a good idea,
- pass the worksheet as an argument, or at least make it an optional argument
- return the range of the found cell, don't activate it in the function, that is not generic

Function fnFind(strFind, Optional sh) As Range
If IsMissing(sh) Then Set sh = ActiveSheet
    On Error Resume Next
    Set fnFind = sh.Cells.Find(What:=strFind, _
                               After:=ActiveCell, _
                               LookIn:=xlValues, _
                               LookAt:=xlPart, _
                               SearchOrder:=xlByRows, _
                               SearchDirection:=xlNext, _
                               MatchCase:=False)
End Function

I would actually add optional, defaulted arguments for all the Find arguments as well.

You would then use it like so

Sub TestfnFind()
Dim SearchFor As Range
    Set SearchFor = fnFind(InputBox("What do you want to search for", "Search"))
    If SearchFor Is Nothing Then
        MsgBox ("Sorry Not Found")
    Else
        MsgBox SearchFor.Value
    End If
End Sub

[Immatoity]

hi, sorry ...

well the code doesn't cut the column with the header Total Sum of TOTAL GOODS BASE VAL when I run the code....

it moves the column with header Sum of TOTAL GOODS BASE VAL

[Bob Phillips]

hi, sorry ...

well the code doesn't cut the column with the header Total Sum of TOTAL GOODS BASE VAL when I run the code....

it moves the column with header Sum of TOTAL GOODS BASE VAL

Okay, you should just need to change

Columns(2).Cut

with

Columns(10).Cut

Could that column move also?

.

[Immatoity]

hi...that works a treat... I am not going to profess to know how it all works...

will this work then when I have a 5 week month? or will it still cut column 10?

I only ask as the end-user will not be able to modify this in vba....

[Bob Phillips]

hi...that works a treat... I am not going to profess to know how it all works...

will this work then when I have a 5 week month? or will it still cut column 10?

I only ask as the end-user will not be able to modify this in vba....

Not if the column to move goes from J to K it won't, this was what I was asking in my previous response. It was fine when I thought it was column 2 to move .

It is easily overcome with

Sub MoveColumn()
    Dim iPos1 As Long
    Dim iPos2 As Long
On Error Resume Next
    iPos1 = Application.Match("Total Sum TOTALVAL", Rows(1), 0)
    iPos2 = Application.Match("Sum of QTY", Rows(1), 0)
    On Error GoTo 0
    If iPos1 > 0 And iPos2 > 0 Then
        Columns(iPos1).Cut
        Columns(iPos2).Insert
    End If
End Sub

[Immatoity]

Hiya

cheers...just tried that ( by first inserting a fifth weeks data in columns F and K resepectively, which means the column I want to move is now column L)... when I run the macro, it doesn't error out, it runs but hasn't moved anything?

[Bob Phillips]

Hiya

cheers...just tried that ( by first inserting a fifth weeks data in columns F and K resepectively, which means the column I want to move is now column L)... when I run the macro, it doesn't error out, it runs but hasn't moved anything?

Are the headings exactly as in the code?

[Immatoity]

hi...derrr..... how stupid am I .... the header was slightly different...amended the code now and it works a treat...

apologies xld...and thanks as usual

[Bob Phillips]

hi...derrr..... how stupid am I .... the header was slightly different...amended the code now and it works a treat...

apologies xld...and thanks as usual

No apologies needed.

Is there some sort of rule that could be applied that can be coded so that the heading doesn't have to be hard-coded? No suggestions off the top from me I am afraid, but maybe you know of something.