Posts by Neiluk66

Neiluk66 · Feb 25th 2008

Re: Reference Part Workbook Name In Formula From Date In Cell

Dave, i got it to work as you said, what are the options if its not practicle to have the workbook open, is there an alternative method?

thanks in advance ,Neil
this is my formula now.
=VLOOKUP(B3,INDIRECT("'[MIR00008_-_Statement_Balances"&G1&".xls]Report1'!$B:$E"),4
,FALSE)

Neiluk66 · Feb 19th 2008

hi,

i want to return a value from a cell in another workbook where part of the the workbook name is determined by a date set on the current open sheet.

so on my open sheet i have a cell where the user can change the date and i want the below formula to then use the date to look at the relevant file.

any ideas would be appreciated. know how to do this in VBA but no clue with formulas.

what i am trying to do is concatenate part of a file name and a cell value which contans a date

=VLOOKUP(A4,[accountsummary17022008.xls]Sheet1!$A:$B,2,FALSE)

cheers
neil

Neiluk66 · Oct 13th 2006

Re: Identify Duplicates

Thats great Kris, works perfectly.

thanks

Neiluk66 · Oct 13th 2006

Hi,

I need to identify duplicates in a list and have the foilowing formula:

=IF(COUNTIF(range1,A2)>1,"Duplicate","")

This works ok but i have a further condition which i dont know how to factor into the formula. I think i could write some vba to determine the dupes but i was hoping to avoid this as im sure it will take me an hour or so.

Duplicates are identified at the moment as being identical numbers in column "amount", i now need to specify duplicates as being identical numbers in this range where there is at least one row with no pay date filled in in col "paydate"

i hope the attached will make things clear.

thanks in advance for your thoughts.

Neil

Neiluk66 · May 27th 2006

Hi,

is there an equivalent of

Code

range("2").PasteSpecial xlPasteValues

when using

Code

.Copy Destination:=

i dont really want to select the paste range but need just the value to be copied

cheers

Neil

Neiluk66 · May 27th 2006

Re: Userform Variables

Norie,

Thanks, i think figured it out now.

Neiluk66 · May 27th 2006

Hi,

Im a bit confused as to how to proceed with my userform and module variables.

I have a Userform and the code does sum calculations on a worksheet then opens another WB and pastes the results.

I have variables for the WB and WS names involved as well as the Userform control values.

I understand that i should declare public variables at the beginning of a mudule if i want them to be available to all sub procedures but what about the userform variables? if i declare these as public in the form will they be available to the module subs? I seem to remember Public variables have to be at the beginning of mudules.

Any thoughts appreciated.

Neil

Neiluk66 · May 26th 2006

Re: Unique List

perfect.

thanks a lot.

Neil

Neiluk66 · May 26th 2006

Re: Unique List

Hi all,

Thanks for your time so far.

Norie, i tried your code and it didnt work because you had missed the criteria off the first advanced filter. It wasnt really what i was looking for and i think i need to explain it better.

What i want to achieve is the same result as having a pivot table but in list format. So a pivot would show a total sum of Kostenstalle "DDUF4"
, where the portlio is "W_001_36432" and so on. I need the data in a list format though.

I think that one can produce a pivot then reverse the process so you get a list but i seem to remember this being a bit complicated and im not that keen on coding pivots if i can avoid it.

Am i making more sense?

My sheet 2 shows exactly the result i want, i have included amounts for clarity.

thanks again to all who responded.

Neiluk66 · May 24th 2006

Hi,

I have two colums of data Col A and Col B.

I want to run an advanced filter on Col A to extract unique values.

I then want to Autofilter Col A by each value in the previously extracted unique list in turn and extract a unique list from Col B.

So far i have extrcated the unique list and applied autofilter.

My questions are:

How do i build a For Each Next loop which goes through each of the extracted unique values and filters on Col A extracting unique values in Col B?

Is there an altogether simpler way of doing this or am i on the right track?

thanks in advance, i attach my slimmed down WB for clarity.

cheers
Neil

code so far is

Code

Sub Unique()


Dim RngPort As Range
Dim RngKost As Range
Dim RngCrit As Range




Set RngPort = Range("a2", Range("a2").End(xlDown))




RngPort.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=RngPort, _
CopyToRange:=Range("D1"), Unique:=True


Set RngCrit = Range("d1", Range("d1").End(xlDown))


Range("A:B").AutoFilter




End Sub

Display More

Neiluk66 · May 19th 2006

SUMIF argument

Hi,

I want to determine the arguments for a SUMIF from my data and parse them (right word?) to the SUMIF function then output the sumif to a messagebox.

Previously i would have run an advanced filter and extracted the unique entries from the list to get the values for the SUMIF but not sure if i can do this in VBA without putting values on the ws.

here is my code where

Code

StrCriteria = "FL_6MGBP"

was used as a test to see if the SUMIF worked. i actually want to determine the SUMIF for each unique entry in column A "Kontahent" attached is a wb with an example, hope this is clear.

and would be grateful for ideas.

Code

Sub sumIF()


Dim Rng1 As Range
Dim Rng2 As Range
Dim StrCriteria
Dim result As Double


Set Rng1 = Range("a2:a5")
Set Rng2 = Range("b2:b5")


StrCriteria = "FL_6MGBP"




result = Application.WorksheetFunction.sumIF(Rng1, StrCriteria, Rng2)


MsgBox result




End Sub

Display More

cheers

Neil

Neiluk66 · May 19th 2006

Re: Sumproduct

Norie,

your right, SUMIF works fine.

I ripped some code from a previous macro where i had mulitple criteria and wanted the formula on the sheet, didnt stop to think how the scenario was different.

Thanks for that.

Neil

Neiluk66 · May 19th 2006

Hi,

I want to use sumproduct in my code and display the result in a msgbox but i dont want to enter the formula on the ws.

i have made an example on the attached ws of the result im looking for in the form of a ws based formula..

error message with code is
"Unable to get the sumproduct property of the worksheet class"

hope someone can give me some pointers as to where im going wrong.

PS been absent from this sight and VBA for some time and surprised i have not been demoted from"senior member!"

cheers
Neil

Code

Sub sumpr()


Dim Rng1 As Range
Dim Rng2 As Range
Dim StrCriteria
Dim result As Double


Set Rng1 = Range("a2:a5")
Set Rng2 = Range("b2:b5")


StrCriteria = "FL_6MGBP"


result = Application.WorksheetFunction.SumProduct((Rng1.AddressLocal) = StrCriteria) * (Rng2.AddressLocal)


MsgBox result




End Sub

Display More

Neiluk66 · May 10th 2006

Re: Worksheet_SelectionChange

Thomach,

I had just decided to actually put some effort into it myself by opening up John Walkenbach's "power programming" and found a chapter on adding code programatically, when you come up with this brilliantly simple solution that means i dont have to read it!

Another time perhaps
Thanks for your time.

Neil

Neiluk66 · May 10th 2006

Re: Worksheet_SelectionChange

Hi Thomach,

I definateley dont want Worksheet_Change event as the user will just be selecting cells and not changing values (i hope!).

im just working with one sheet at a time. I mentioned specifying a worksheet to monitor the changes because i was thinking you might be able to put code equivalent to "sheet1"Worksheet_SelectionChange ... in a module or userform code to avoid adding the sub to each worksheet that is created.

how difficult is it to add a Worksheet_SelectionChange sub to a new sheet programatically?

thanks for the input
cheers

Neil

Neiluk66 · May 10th 2006

Hi,

I want to use the Worksheet_SelectionChange event to run a macro. - a simple sum of values on the sheet that will be displayed in a userform.

Problem is the worksheet that the calculation will be run on, and that the user will be selecting cells from, will be created by the user.

I beleive its possible to create a macro programatically and add it to the worksheet, although i have never done this, but is there an alternative?

Can you specify a worksheet that you want to monitor the selection change and thereby trigger the macro?

this is my code which is currently run by a button on a form

Code

Sub CalcRunningBro()


Dim RngSelect As Range
Dim C As Range
Dim BroRunningTotal As Double
Dim CBro As Double




Set RngSelect = Selection




BroRunningTotal = 0


For Each C In RngSelect




CBro = Cells(C.Row, 22).Value


BroRunningTotal = BroRunningTotal + CBro


Next C
TxtBro.Value = BroRunningTotal


End Sub

Display More

cheers for thoughts
Neil

Neiluk66 · Apr 24th 2006

Re: Subtract month VBA

Thats great Norie,

I adapted to give the month in long format.

been out of the game for too long, forgetting the basics!

Code

MyMonth = Format(DateAdd("m", -1, Date), "mmmm")

cheers

Neil

Neiluk66 · Apr 24th 2006

Hi,

I want to return the previous month in long text format and then use it as part of a file name.

when i run the below the subtraction takes one day away from the current date and the msgbox displays "January"

I want to return "March"

Can someone help please?

Code

sub test
Dim MyMonth
Dim TradesMonth
MyMonth = Month(Date) - 1


TradesMonth = Format(MyMonth, "mmmm")


msgbox TradesMonth
end sub

Display More

thaks
Neil

Neiluk66 · Apr 23rd 2006

Re: Recorded code default to existing module

Roy,

I think your right.

I usually delete the modules as soon as i am finished with the recorded code which ironically is why i keep having to do this. If only i had left the first one alone once it had been created it!

thanks

neil

Neiluk66 · Apr 23rd 2006

Hi,

I was wondering if it is possible to have recored macro code appear in an existing module rather than always appearing in a new module.

cheers

Neil