Wrox Programmer Forums
|
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
 
Old October 18th, 2010, 06:42 AM
Registered User
 
Join Date: Oct 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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 asked more than 3 forums about my need and no 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.
Many thanks in advance.
 
Old October 20th, 2010, 07:26 AM
Registered User
 
Join Date: Oct 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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.
 
Old October 26th, 2010, 07:06 PM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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
 
Old October 27th, 2010, 03:08 AM
Registered User
 
Join Date: Oct 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear sir,with my thanks,this message appears after pressing the macro button :
( compile error:Expected EndWith).
Where is the problem , please.
 
Old October 27th, 2010, 03:13 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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
 
Old October 27th, 2010, 03:58 AM
Registered User
 
Join Date: Oct 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Dear sir,there is no ( debug) in this message,only ( information & accept).
What can i change in code to work,please.
 
Old October 27th, 2010, 08:52 AM
Registered User
 
Join Date: Oct 2010
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
 
Old October 27th, 2010, 09:47 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default

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
 
Old October 27th, 2010, 10:00 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default 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.
 
Old October 27th, 2010, 11:09 AM
Friend of Wrox
 
Join Date: Sep 2010
Posts: 171
Thanks: 0
Thanked 14 Times in 14 Posts
Default 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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.