UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Not Sure On The Type Of VBA I Need, Office 2010    
 
   
wornout
post Feb 14 2018, 01:25 PM
Post#1



Posts: 1,035
Joined: 17-November 13
From: Orewa New Zealand


From the sheet "Service Invoice" table "Invoice" on Product selection change
I want to
1) Lookup worksheet Stock Table2 Item
2) if it matches then in the matching row
3) Table2 sold = Invoice "Quantity" (Quantity in the row that matches the product)
Table2 Instock = Instock - Invoice Quantity (Quantity in the row that matches the product)
Table2 Totalsold = Total sold + Invoice Quantity (Quantity in the row that matches the product)
Go to the top of the page
 
wornout
post Feb 14 2018, 04:30 PM
Post#2



Posts: 1,035
Joined: 17-November 13
From: Orewa New Zealand


After hours of trying and a lot of deb.print I got it
Now amount sold on table2 changes to quantity in invoice table
Instock changes by Quantity amount
TotalSold changes by quantity
here is what I put it is probley way to long and I most likley have far to much thats not needed but it works
CODE
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim myRange As Range
Dim OurRange As Range
Dim FindString As String
Dim Rng As Range
Dim QRange As Variant
Dim WRange As Variant

Set myRange = Worksheets("Service Invoice").Range("Invoice[Product]")
Set OurRange = Worksheets("Service Invoice").Range("Invoice[Job]")

If Not Application.Intersect(Target, myRange) Is Nothing Then
If Application.Intersect(Target, myRange).Offset(0, 1).Value = vbNullString Then
Set WRange = Application.Intersect(Target, myRange)
Debug.Print WRange.Value; "Products"
Else
       MsgBox "You can not enter a job and product on the same Row"

End If

End If
If Not Application.Intersect(Target, OurRange) Is Nothing Then
If Application.Intersect(Target, OurRange).Offset(0, -1).Value = vbNullString Then
  
Else
       MsgBox "You can not enter a job and product on the same Row"

End If

End If

'It was from here down that does what I want it to do with the stock sheet

If Not Application.Intersect(Target, myRange) Is Nothing Then
Application.Intersect(Target, myRange).Offset(0, 2).Select
QRange = Selection.Value
Debug.Print QRange; "QRange"

FindString = WRange

If Trim(FindString) <> "" Then
    With Sheets("Stock").Range("A:A") 'searches all of column A
        Set Rng = .Find(What:=FindString, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlValues, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
                        
                    

                    
         If Not Rng Is Nothing Then
        Application.Goto Rng, True 'value found
            Rng.Select
            Selection.Offset(0, 6).Select
    Debug.Print Selection.Address; " stockSold"
    Selection = QRange
    Selection.Offset(0, -1).Select
    Selection = Selection.Value - QRange
    Debug.Print Selection.Address; " Reducestock"
    Selection.Offset(0, 2).Select
    Selection = Selection.Value + QRange
    Debug.Print Selection.Address; " TotalStock"
        Else
          MsgBox "Nothing found" 'value not found
                    
        End If
    End With









End If
End If
End Sub
Go to the top of the page
 
larai
post Feb 15 2018, 03:07 PM
Post#3



Posts: 1,107
Joined: 8-February 02
From: California, USA


Couldn't you have done this with vlookup formulas?
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st May 2018 - 12:19 AM