Cell reference to locate /store data in excel [Solved/Closed]

Report
-
 suryam -
Hello,


Hello ,
I am working for a newly started airline agency. I am enclosing my working file (for privacy sake , I have changed names and other things). when I press the save button(macro is there in it) on my "Invoice" sheet, the invoice will be saved as it is one below the other in order the of the invoice number in "INVOICE STORE" .And I am maintaining the invoice numbers and some other details in another sheet "Invoices List" and also when I press "SAVE DATA " button, the "SELECTED DATA" will be stored in it. Now my questions are
1)when I press on the invoice number in INVOICES LIST sheet....I must be able to GO EXACTLY TO the correct invoice location in the INVOICE STORE sheet(so far I am using ctrl+F to locate it...I donot want to use it anymore) for example , if I press invoice No. 6454, I must be able to go exactly to the location of the stored invoice.

2) I need to build a log of some of the selected entries of my INVOICE sheet.I need a vb macro to do this task.
Is there any way to save(in another new sheet) the entries in the following order in a row...one below the other
a)company name b) invoice No. b)delivery date c)traveller name d)airlines e) particulars f)total amount

note: here it should be applicable to all the traveller names in that particular invoice...I mean , if there are four travellers...there must be four rows in the stored sheet with same invoice No. and same company name but with different traveller names and details.

for example:
COMPANY-NAME INVOICE No DEL DATE TRAVELLER NAME AIRLINES PARTICULARS AMOUT
CELESTINE JOHN 6452 1-Apr-11 PHILIP VAN DER B Mr AF CAN-PAR-CAN $2,334.00
CELESTINE JOHN 6452 1-Apr-11 PHILIP Mrs AF PAR-CAN $1,500.00

like this....this procedure should continue when I create new invoice and so on.

Hope I am successful in conveying my idea.please help me with vb macro code. I am not good at vb programming.
please help me in this matter,,,,thanks Mr.Suryam

8 replies

Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Hi Suryam,

Finally i got codes for your problem. i have make some changes in your already defined macros, if the total value of invoice is zero than no data copies to invoice & data sheets and a msg displayed that " No new Invoice".

now come to your query.

qns 1) I generate a code to put hyperlink at each invoice no. by clicking insert hyperlink button. now when u click on invoice no. it link you to that particular invoice in invoice store sheet.

Qns 2) I put some code in Button221_Click() now when you click save invoice button it generate a new invoice next to privious one.

Qns 3) I make a new Sheet Data Reqd and a new button " Press to Save Data Reqd" in Sheet Invoice.

Link of your sheet is :
http://wikisend.com/download/602234/sales report model.xls

the codes are as below:
Sub InvoiceLocation()
Dim InvoiceNo, CellRef As String
Dim AC, rngOR As Range

Application.ScreenUpdating = False

For Each AC In Range("D5:D65536")
    If (AC.Value = vbNullString) Then Exit Sub
    With Sheets("Invoice store")
      Set rngOR = Nothing
      Set rngOR = .Cells.Find(What:=AC.Value, _
                                 After:=.Cells(1, 1), _
                                 Lookat:=xlPart, _
                                 SearchDirection:=xlNext, _
                                 MatchCase:=False)
      If (rngOR Is Nothing) Then Exit Sub
      CellRef = rngOR.Address
    
    End With
    AC.Select
    InvoiceNo = "'INVOICE STORE'!" & rngOR.Address
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    InvoiceNo

Next AC

Application.ScreenUpdating = True

End Sub


Sub DataRqd()
Dim SourceWS    As Worksheet, DestWS As Worksheet
    Dim SourceRng, DestRow, CellDesc   As Range
    Dim lloop As Long
     
    Set SourceWS = Sheets("INVOICE") 'adjust the sheet name
    Set DestWS = Sheets("DATA REQD") 'adjust the sheet name
    If SourceWS.Range("F41") = 0 Then
    MsgBox "NO NEW DATA TO SAVE !!", vbExclamation + vbInformation, "INVOCE SAVED INTO INVOICE STORE"
    Exit Sub
    Else
    For Each CellDesc In SourceWS.Range("B31:B40")
    If CellDesc.Value = "" Then Exit For
    With SourceWS
            Set SourceRng = .Range("F15")
            Set DestRow = DestWS.Range("A" & Rows.Count).End(xlUp).Offset(1)
            SourceRng.Copy DestRow
            SourceRng.Copy
            DestRow.PasteSpecial xlPasteValuesAndNumberFormats
        For lloop = 1 To 3
            Set SourceRng = Choose(lloop, .Range("F17"), .Range("C15"), _
               Range(CellDesc, CellDesc.Offset(0, 3))) 'adjust the range
            Set DestRow = DestRow.Offset(0, 1)
            SourceRng.Copy DestRow
            SourceRng.Copy
            DestRow.PasteSpecial xlPasteValuesAndNumberFormats
            If lloop = 1 Then
            With DestRow
            .Value = Mid(DestRow, 9, 4)
            .NumberFormat = "General"
            End With
            End If
            Application.CutCopyMode = False
        Next lloop
    End With
    Next CellDesc
    MsgBox " DATA SAVED !!!", vbExclamation + vbInformation, "DATA SAVED TO DATA SHEET"
    End If

End Sub
2
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
Could you please upload a sample EXCEL file WITH sample data, macro, formula , conditional formatting etc on some shared site like https://authentification.site , http://docs.google.com, http://wikisend.com/ , http://www.editgrid.com etc
AND post back here the link to allow better understanding of how it is now and how you foresee. Based on the sample book, could you re-explain your problem too.


Note: your data need not be to be real data but a good representative of how data looks like
hi I have uploaded my sample .xls file at this location .
http://wikisend.com/download/354188/sales report model.xlsa
I have placed my questions on the first sheet of my .xls work book.
pls help me.
thanks Suryam
hI,
YOUR CODE IS FANTASTIC. my problem was solved without doubt. really you are great. really I appreciate your knowledge and thank you very much for all your help. you have reduced my work greatly.
thanks again
cheers
bye suryam
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
You are most welcomed.

Cheers, :)
Rahul
Hi , it's me again. I have a new question now. I will have traveller /passenger names who travel frequently (or having travelled atleast once by buying tickets from our agency) in my sales report. I have a worksheet containing names of the persons who took tickets from our agency. while creating a new invoice for a company/person...I need to type the names of the passengers under the "description" in the column B (b31:b42). pls refer to my sales report which is located at http://wikisend.com/download/354188/sales report model.xlsa .
Is there any way to get the names of the existing travellers(who are our old customers) while typing the names in the column range b31 to b42? I mean to say...suppose that Rahul is our old customer....and I am preparing a new invoice for his new travel trip . I need to type Rahul ...while typing R..can I get "PROMPTING" for the names of the persons whose starting letter is R? I may be crazy...but it helps me a lot ...pls help..bye suryam
Posts
368
Registration date
Monday February 7, 2011
Status
Contributor
Last seen
September 30, 2013
120
hi Suryam,

Always Post your new Query in a new thread.

Regards,
Rahul
Hi thanks ,
I will
thanks
suryam