PDA

View Full Version : Solved: Excel Functions VBA



fredlo2010
05-22-2012, 09:20 PM
Hello,

Ok i have this long a complex function I created to check for a couple of things. I decided to turn it into a function; its really long and bulky.

but i dont even know where to start

here is my formula

Function CheckCodes(varData As Integer)


CheckCodes =IFERROR(IF(A4=IFERROR(VLOOKUP(A4,oldcodes,2,FALSE),""),"Incorrect Code"&","&" please use "&VLOOKUP(A4,oldcodes,3,FALSE),VLOOKUP(A4,BOM,2,FALSE)),IF(OR(A4="New",A4="Cobalt",A4="Items",A4=0),"","Could not be found!"))


End Function

GTO
05-22-2012, 09:26 PM
Hi Fred,

I have never used IFERROR, but it comes up under Application.WorksheetFunction, so I think it should work. You do seem to have arguments that are not passed to the function: 'oldcodes' and 'BOM'. On the other hand, I do not see where you used the one parameter 'varData'?

Mark

Bob Phillips
05-23-2012, 12:44 AM
It should be simple, but that formula makes no sense to me. AT one point you have this check

IF(A4=IFERROR(VLOOKUP(A4,oldcodes,2,FALSE),""),

It is hard to see how this can be true, as you use A4 to lookup in column 1 of oldcodes and return column2, which surely will never be the same?

fredlo2010
05-23-2012, 08:00 AM
It should be simple, but that formula makes no sense to me. AT one point you have this check

IF(A4=IFERROR(VLOOKUP(A4,oldcodes,2,FALSE),""),

It is hard to see how this can be true, as you use A4 to lookup in column 1 of oldcodes and return column2, which surely will never be the same?


XLD,

In fact it is true the reference for old codes has column 2 repeated because I was not sure I could search for a value on the same column.

The statement is if you look in " Oldcodes" and you find it then.....


thanks

Bob Phillips
05-23-2012, 09:19 AM
Why didn't you just use column 1

IFERROR(VLOOKUP(A4,oldCodes,1,False),...

but it better to use

IF(ISNA(MATCH(A4,oldCodes,0)),...

fredlo2010
05-23-2012, 12:18 PM
OK I really need help,

After I added several Userforms that run different macros (very simple copy and paste most of them) my file grew enormously. Then since it was so big

1. I created an add in with all the forms and form related macros
2. I Linked the main WorkBook with another one that contains some data I have to vlookup or validate.

What’s happening now is that my file and macros take forever to run. Any ideas??? Suggestions?.


Some of my reference formulas can be very complicated

=IFERROR(IF(A4=IFERROR(VLOOKUP(A4,'C:\SourceFolder\[Reference Workbook.xlsx]Old Codes'!$A:$C,2,FALSE),""),"Old code"&","&" please use "&VLOOKUP(A4,'C:\SourceFolder\[Reference Workbook.xlsx]Old Codes'!$A:$C,3,FALSE),VLOOKUP(A4,'C:\SourceFolder\[Reference Workbook.xlsx]BOM Reference'!$A:$L,2,FALSE)),IF(OR(A4="Cookies",A4="Accessories",A4="Items",A4=0),"","Item could not be found!"))


=IFERROR(IF(AND(A4<>0,VLOOKUP(A4,'C:\SourceFolder\[Reference Workbook.xlsx]BOM Reference'!$A:$L,9,FALSE)=0),"Update!",VLOOKUP(A4,'C:\SourceFolder\[Reference Workbook.xlsx]BOM Reference'!$A:$L,9,FALSE)),"")


=IF(A4="","",IFERROR(VLOOKUP(A4,'C:\SourceFolder\[Reference Workbook.xlsx]BOM Reference'!$A:$L,10,FALSE)*VLOOKUP(A4,'C:\SourceFolder\[Reference Workbook.xlsx]BOM Reference'!$A:$L,11,FALSE),0))

I am biting off a little more than I can chew… I think

Thanks for the help

:dunno

Paul_Hossler
05-23-2012, 07:41 PM
One thing to think about is a user defined function. You can use it in a worksheet or called from another sub

You need to put it where the programs can find it

I'm not good enough to sort my way thru long WS formulas, so this is not your answer. Just an example of how you might use a UDF.



Option Explicit

Function NewCode(OldCode As Variant, OldCodes As Range) As String
Dim vResult As Variant

If OldCode = "Cookies" Or OldCode = "Accessories" Or OldCode = "Items" Then
NewCode = vbNullString
Exit Function
End If



With Application.WorksheetFunction
On Error GoTo NotFound
vResult = .VLookup(OldCode, OldCodes, 2, False)

On Error GoTo NoNewCode
vResult = .VLookup(OldCode, OldCodes, 3, False)
End With

On Error GoTo 0
Err.Clear
NewCode = "Please use " & vResult & " instead"

Exit Function

NoNewCode:
On Error GoTo 0
Err.Clear
NewCode = "No new code for " & OldCode
Exit Function

NotFound:
On Error GoTo 0
Err.Clear
NewCode = OldCode & " cannot be found"
End Function


You can get to most WS functions, but I prefer the structured VBA since I find it easier to debug

Paul

fredlo2010
05-24-2012, 06:23 AM
Hi Paul,

thanks for the help it has really helped me a lot to understand functions. Beyond whats on the books of "varResult=varItems*varPrice"

for some reason I am not getting it to work properly thought I always get Value error.

I have tried several things but nothing has worked also i tried to implement another range and workbook " BOM Reference" but I got the same Value error.

Thanks

Paul_Hossler
05-24-2012, 08:57 AM
Without seeing it, the only thing I could suggest is that you're not passing OldCodes as Range, but as a string :dunno

Post a small sample workbook xlsm and people will be able to offer more suggestions and ideas


Paul

fredlo2010
05-24-2012, 10:04 AM
Thanks Paul

I have attached a file with what I want to accomplish.

BTW I want "Items" and "Cookies" to have nothing next to it because they will act as sub-headers.


Here is the attachement will some data enterd to show what I want to achieve. Just unzip the attachment into the C:\ drive to make sure all the linked cells work properly

8135

Thanks

Paul_Hossler
05-24-2012, 11:48 AM
that helps (BTW that was a RAR file)


Option Explicit

'=NewCode(A2,[Reference.xlsx]OldCodes!$B:$D)

Const gcCode As Long = 1
Const gcNewCode As Long = 2
Const gcName As Long = 3

Function NewCode(OldCode As Variant, OldCodes As Range) As String

Dim iResult As Long

If OldCode = "Cookies" Or OldCode = "Accessories" Or OldCode = "Items" Then
NewCode = vbNullString
Exit Function
End If


With Application.WorksheetFunction

'see if it already has the new code
iResult = 0
On Error Resume Next
iResult = .Match(OldCode, OldCodes.Columns(gcNewCode), 0)
On Error GoTo 0

If iResult > 0 Then
NewCode = OldCodes.Cells(iResult, gcName).Value
Exit Function
End If

'see it is an old code
iResult = 0
On Error Resume Next
iResult = .Match(OldCode, OldCodes.Columns(gcCode), 0)
On Error GoTo 0

If iResult > 0 Then
NewCode = "Please use " & OldCodes.Cells(iResult, gcNewCode).Value & " instead"
Exit Function
Else
NewCode = "Item could not be found"
End If

End With

End Function


Try something like this. Might get you closer.

Personally, I prefer a user defined function when the WS formula gets too long

Paul

fredlo2010
05-24-2012, 01:28 PM
Paul,

Have you tested the script? It does not work for me I still get the same "VALUE" error.

maybe you can post the file as well?

Thanks

And yes it was a rar file and I renamed it. I did the archive in a hurry and I totally forgot how to create a zip file...as i am wrting I am trying to think how and I dont know. :rotlaugh:

Paul_Hossler
05-24-2012, 05:04 PM
No problem, even if I did get a speeding ticket from the FHP last time I was there

I did notice that your references within the Vlook's jumped around



in B4

=IFERROR(IF(A4=IFERROR(VLOOKUP(A4,'C:\Users\Daddy\Desktop\New Folder\[Reference.xlsx]OldCodes'!$A6:$C155,2,FALSE),""),"Old code"&","&" please use "&VLOOKUP(A6,'C:\Users\Daddy\Desktop\New Folder\[Reference.xlsx]OldCodes'!$A6:$C155,3,FALSE),VLOOKUP(A4,'C:\Users\Daddy\Desktop\New Folder\[Reference.xlsx]Codes'!$A6:$L10005,2,FALSE)),IF(OR(A4="Cookies",A4="Accessories",A4="Items",A4=0),"","Item could not be found!"))

and in B5


=IFERROR(IF(A5=IFERROR(VLOOKUP(A5,'C:\Users\Daddy\Desktop\New Folder\[Reference.xlsx]OldCodes'!$A7:$C156,2,FALSE),""),"Old code"&","&" please use "&VLOOKUP(A7,'C:\Users\Daddy\Desktop\New Folder\[Reference.xlsx]OldCodes'!$A7:$C156,3,FALSE),VLOOKUP(A5,'C:\Users\Daddy\Desktop\New Folder\[Reference.xlsx]Codes'!$A7:$L10006,2,FALSE)),IF(OR(A5="Cookies",A5="Accessories",A5="Items",A5=0),"","Item could not be found!"))


That could lead to not finding matchs :think:

For ex, 554448 looks like it maps to 553925 (row 9 on 'OldCodes') so I don't know if your "Item cannot be found" is correct. All depends on what you want to do

(BTW, 'Daddy' is my user ID on the home PC)

Here's the files in RAR renamed as a ZIP

As long as both WBs are open, I don't seem to get #Value



Paul

Paul_Hossler
05-24-2012, 05:10 PM
and ...

fredlo2010
05-24-2012, 09:13 PM
Hi Paul,

It works perfectly. and regarding the problem with it mapping different codes was a typo error, it was supposed to use "A4" as reference all the time.

I think I will read what I want to achieve to you so you can understand a little bit better.

the part with the making the word bold is perfect.

But i need to maake referece to two ranges instead of one

1: The old Codes
2: The codes

look for value on cell "A4"

Check in the range "Old Codes" if you find it display the message informing to use the code thats on the column 3 of this range.

if you cannot match any value by lookking in range "Old Codes" then look in the range "Codes" to find the description.

Note the range "Codes" is huge thousand of rows and the "Old Codes" is a very small 150 rows one.

Thanks a lot for the help. Wow this is hard.

Paul_Hossler
05-25-2012, 05:24 AM
Gald it got you started

The rest is left as an exercise to the reader :thumb

Come back if you have more questions

Paul

fredlo2010
05-25-2012, 09:49 AM
Thanks for the help. I will make sure i come back...to the best VBA Forums ever