code for formulas

tofimoon4 · October 18th, 2010, 06:42 AM

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.

tofimoon4 · October 20th, 2010, 07:26 AM

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.

mtranchi · October 26th, 2010, 07:06 PM

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

tofimoon4 · October 27th, 2010, 03:08 AM

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

mtranchi · October 27th, 2010, 03:13 AM

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

tofimoon4 · October 27th, 2010, 03:58 AM

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

tofimoon4 · October 27th, 2010, 08:52 AM

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

mtranchi · October 27th, 2010, 09:47 AM

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

mtranchi · October 27th, 2010, 10:00 AM

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.

mtranchi · October 27th, 2010, 11:09 AM

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