Wrox Programmer Forums code for formulas
 |
 Excel VBA Discuss using VBA for Excel programming.
 Welcome to the p2p.wrox.com Forums. You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com

October 18th, 2010, 06:42 AM
 tofimoon4 Registered User Join Date: Oct 2010 Posts: 6 Thanks: 0 Thanked 0 Times in 0 Posts
code for formulas

Dear sir,with my regards,this is my first post to your excellent forum and hopping to get any reply .
i have in my worksheet, ranges (h5:h1000)this formula :IF(F5="";"";VLOOKUP(F5;prices;2;0)) , and in range ( i5:i1000) this one :IF(F5="";"";G5*H5).
I need your help by a macro to get the results without formulas in the worksheet, i mean when i press a button the results of these formulas will appear directly.

October 20th, 2010, 07:26 AM
 tofimoon4 Registered User Join Date: Oct 2010 Posts: 6 Thanks: 0 Thanked 0 Times in 0 Posts
code for formulas

Dear sir,with my regards,i want to ask if there is any expert in this forum to answer the members questions,as i have seen more than 60 members had viewing this thread without any reply.
I hope i am coming to the right forum.

October 26th, 2010, 07:06 PM
 mtranchi Friend of Wrox Join Date: Sep 2010 Posts: 171 Thanks: 0 Thanked 14 Times in 14 Posts

in code you would do it like so:

Code:
```Sub LookupValues()

Dim Rg1 As Range
Dim i As Long

Set Rg1 = Sheets("sheetname").Range("F5")

With Rg1
For i = 0 To 955 'You may have to adjust the 955 to 956 or 954
If .Offset(i, 0) = "" Then

.Offset(i, 2) = WorksheetFunction.VLookup(.Offset(i, 0), Range("prices"), 2, 0)
.Offset(i, 3) = .Offset(i, 1) * .Offset(i, 2)
End If
Next i
End Sub```

October 27th, 2010, 03:08 AM
 tofimoon4 Registered User Join Date: Oct 2010 Posts: 6 Thanks: 0 Thanked 0 Times in 0 Posts

Dear sir,with my thanks,this message appears after pressing the macro button :
( compile error:Expected EndWith).
Where is the problem , please.

October 27th, 2010, 03:13 AM
 mtranchi Friend of Wrox Join Date: Sep 2010 Posts: 171 Thanks: 0 Thanked 14 Times in 14 Posts

hit debug when that message comes up. in the subroutine it brings you to you have a With without a closing End With

this is what the code should look like
Code:
```sub test()

With range("a1")
.copy 'this is just made up code
End with 'this is what your code is missing```

October 27th, 2010, 03:58 AM
 tofimoon4 Registered User Join Date: Oct 2010 Posts: 6 Thanks: 0 Thanked 0 Times in 0 Posts

Dear sir,there is no ( debug) in this message,only ( information & accept).
What can i change in code to work,please.

October 27th, 2010, 08:52 AM
 tofimoon4 Registered User Join Date: Oct 2010 Posts: 6 Thanks: 0 Thanked 0 Times in 0 Posts

Dear sir,this schedule represents a bit from my worksheet ,the formulas are in (H5-H1000, I5-I1000).
i hope this will help.
Many thanks.

Col B C D F G H I Rows
4 Date invoice agent Mat. Qua. Price Cost
5 E 10 5 500
6 A 100 6 600
7 D 200 4 800
8 C 450 2 900

October 27th, 2010, 09:47 AM
 mtranchi Friend of Wrox Join Date: Sep 2010 Posts: 171 Thanks: 0 Thanked 14 Times in 14 Posts

Go the the visual basic editor, Hit Edit>Find and type in With, make sure the entire project is selected, then hit find. For each one you find, scan down and make sure each one has an End With. If you're not familiar with programiming, it may be difficult to know where to place the End With, if you put it just before the next instance of a With statement or before the End Sub, you should be ok

October 27th, 2010, 10:00 AM
 mtranchi Friend of Wrox Join Date: Sep 2010 Posts: 171 Thanks: 0 Thanked 14 Times in 14 Posts
End with

you should have started a new thread for this End With situation. Makes it confusing for other people with the same problem who do a google search and come to this page. Anyway, I just remembered there's a quirk in VBA. Hit the play button while you're in the Visual Basic Editor (VBE) and it should bring you close to where the issue is. The problem is that sometimes the VBA compiler will confuse If/End if, Select Case/End Select, For/Next and Do/Loop with With/End With. So if you search through and can't find a missing End With, look for a missing end tag for one of the above. If you can't find it, post a new thread and post all the code in the sub that VBA brings you to when you hit the play button and I'll find the problem for you.

October 27th, 2010, 11:09 AM
 mtranchi Friend of Wrox Join Date: Sep 2010 Posts: 171 Thanks: 0 Thanked 14 Times in 14 Posts
OH, LOL

The problem is in MY code, lol. sry. it should look like so.
Code:
```Sub LookupValues()
Dim Rg1 As Range
Dim i As Long

Set Rg1 = Sheets("sheet3").Range("F5")

With Rg1
For i = 0 To 955 'You may have to adjust the 955 to 956 or 954
If .Offset(i, 0) <> "" Then 'This should be changed from "=" to "<>"
.Offset(i, 1) = WorksheetFunction.VLookup(.Offset(i, 0), Range("prices"), 2, False)
.Offset(i, 3) = .Offset(i, 1) * .Offset(i, 2)
End If
Next i
End With '<<<<<<<<The missing End with
End Sub```
sorry, it was late last nite and i didn't test the code before posting.

also, it sounds like what you've got is a situation where you put in an item and quantity and you want the total price to pop up, so you don't want to have vba calculate all one thousand lines if you only put in two or three items.

Here's code that needs to go into the module behind the worksheet you're using that will accomplish what you want more effiiciently.

Code:
```Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
With Target
If .Columns.Count > 1 Or .Rows.Count > 1 Then Exit Sub
If .Column = 6 And .Value <> "" And .Offset(0, 1) > 0 Then 'it's column "F" and there is something in that column as well as a quantity in column "G"
.Offset(0, 2) = WorksheetFunction.VLookup(.Offset(0, 0), Range("prices"), 2, False)
.Offset(0, 3) = .Offset(0, 1) * .Offset(0, 2)
ElseIf .Column = 7 And .Offset(0, -1).Value <> "" And .Value > 1 Then 'it's colunm "G" and there is something in column "F" as well as a quantity in column "G"
.Offset(0, 1) = WorksheetFunction.VLookup(.Offset(0, -1), Range("prices"), 2, False)
.Offset(0, 2) = .Offset(0, 0) * .Offset(0, 1)
End If
End With
Application.EnableEvents = True
End Sub```
that should do it far more efficiently. I tested it before posting this time, so it works in my spreadsheet anyway. In the visual basic editor, double click on the spreadsheet you have your formulas right now and paste it there. Hope that helps

 Similar Threads Thread Thread Starter Forum Replies Last Post Formulas Juan0214 Access 4 July 2nd, 2008 02:04 PM Formulas work in preview not online andiadmin Infopath 0 April 16th, 2007 12:01 PM Need help with time formulas pfreire727 Excel VBA 1 February 12th, 2006 07:41 PM Inputbox and Formulas bahachin Excel VBA 3 February 17th, 2005 01:39 AM