PDA

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



Immatoity
09-13-2005, 04:05 AM
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
09-13-2005, 04:57 AM
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
09-13-2005, 04:58 AM
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
09-13-2005, 05:18 AM
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
09-13-2005, 06:16 AM
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
09-13-2005, 06:21 AM
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
09-13-2005, 06:33 AM
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
09-13-2005, 06:56 AM
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
09-13-2005, 07:09 AM
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
09-13-2005, 07:27 AM
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
09-13-2005, 07:38 AM
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 http://vbaexpress.com/forum/images/smilies/sad2.gif.

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
09-13-2005, 07:45 AM
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
09-13-2005, 09:05 AM
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
09-14-2005, 02:03 AM
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
09-14-2005, 02:39 AM
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.