[RESOLVED] VBA in Excel

Scuse my ignorance but am a newbie, I hope someone can help?
I want to write a macro that will select a cell by criteria Ie, what is in that cell (x), record data on the corresponding row, within certain cells only and then write it to another sheet in the same workbook beneath an existing set of rows with similar data, but into a different order of cells on the new row. I have created a button that triggers an input box so when i enter the data (x) this starts the process.

I'm sure this is simple for you folks in the know so all help would be appreciated

Regards

Dom

This is where i'm at:
Private Sub CommandButton1_Click()
Dim x As Variant, i As Integer, jobnum As String, jobval As Long
jobnum = InputBox("Insert a Job Number")
i = 0
Do
i = i + 1
Loop Until Cells(i, 1).Value = jobnum
Range("A133").Value = jobnum ' another sheet at base of selection?
End Sub

'For i = 1 To 50
'If Cells(i, 1).Value = jobnum Then
I was playing around with this - 'MsgBox ("recording new Project")
'Else: MsgBox ("Cannot Find JobNumber")
'End If
'Next i

Some issues as stated by my friends (thanks for the post), controlling user input - therefore I need to put in some kind of loop back to the beginning !!!loop within a loop!!! with a message box stating that job number not found.
next bit is copying the cells adjacent and then pasting them at the bottom of a set range on another sheet. I've read about dynamic selections but just not sure.

PLEASE ALL HAVE SYMPATHY I'M JUST 2 WEEKS INTO THIS VBA THING


Thanks Dom

What code have you tried?
Post up (and put the code tags around it).

Also - excel has a record macro function. Use this to get a general idea of how you want it to process and change the code to use objects and process the way you want it to.

Dom:

Are you dealing with Numeric data or Text data. You are going to have fun validating the user input ... you never can predict all the weird combinations a user will enter. Is the criteria data sorted? Which column is it in? Here is an example of the tasks you will probably need to accomplish:

Code:

---

Option Explicit
Sub InputBoxHandler()

'Sanity check on Input Data
'Search "Sheet 1" for applicable Data Row
'Stuff appropriate data into Data Row
'Find bottom row of Destination Sheet
'Copy Data Row elements into Destination Row

End Sub

---

Have you defined the Maps ... which cells from the row on "Sheet 1" go into which cells on the "Destination" sheet? Why don't you put the map info in your next post.

Thanks guys, have posted a little more info as an addendum but still would like to hear from anyone else who can offer a solution. So far am making small progress day by day and the learning curve is pretty steep! love to hear from anyone who can offer a little help. and thanks for repys so far

Quote:

---

Originally Posted by desmondno1

This is where i'm at:

Code:

---

Private Sub CommandButton1_Click()
Dim x As Variant, i As Integer, jobnum As String, jobval As Long
jobnum = InputBox("Insert a Job Number")
    i = 0
        Do
            i = i + 1
        Loop Until Cells(i, 1).Value = jobnum
    Range("A133").Value = jobnum ' another sheet at base of selection?
End Sub

                                              'For i = 1 To 50
                                              'If Cells(i, 1).Value = jobnum Then
'  I was playing around with this -  'MsgBox ("recording new Project")
                                              'Else: MsgBox ("Cannot Find JobNumber")
                                              'End If
                                              'Next i

---

Some issues as stated by my friends (thanks for the post), controlling user input - therefore I need to put in some kind of loop back to the beginning !!!loop within a loop!!! with a message box stating that job number not found.

---

Leave that for now. Come back to it after reading up on string manipulation, and sub/functions. You need to define a set of rules to validate the users input, against what data type you were expecting...

Quote:

---

next bit is copying the cells adjacent and then pasting them at the bottom of a set range on another sheet. I've read about dynamic selections but just not sure.

---

This is fairly easy.
In Excel (and Word) things depend on ranges. In Excel a selection of one or more cells is a range. If you put the next bit of code into a new module, and run it (on an empty workbook).

Code:

---

public sub RunDis()
    dim sht as worksheet
    dim rng as range

    on error resume next

'---- set the sht and the range in that sheet (with proper objects)
'---- the record macro function ALWAYS uses selection object
'---- steer clear of it if you can! ;)
    set sht = activeworksheet
    set rng = sht.range(sht.cells(1,1),sht.cells(1,6)

'---- select the range
'---- as a demonstration - you can use any properties of the range instead
    rng.select

    set rng=nothing
    set sht= nothing

end sub

---

Thanks for the reply I couldnt get it to work.
The user input as you say needs further development but as its plain on the excel sheet that should be no bother for the mo'.
Below represent my excel data
A B C
1 R0232 Y £1000.00
2 R0233 Y £450.00
3 R0234 Y £1155.00
4 R0235 Y £985.00

What I really need to get my head around is the code for matching the data on column C with the Job Number i.e, R0235 & £985.00

I am going to then move that data to the sheet "Invoiced Sales Report" but I need to put it on a new line after all other entries. I want to put the job number "R0235" in column A and the value £985.00 in Column F

This is half way there I think!

Private Sub CommandButton1_Click()
Dim X As Variant, i As Integer, Jobnum As String, Jobval As Long
Jobnum = InputBox("Insert a Job Number")
Sheets("Invoiced Sales Report").Select
Sheets("Invoiced Sales Report").Range("A65536").End(xlUp).Offset().Select
Selection.EntireRow.Insert
ActiveCell.Value = Jobnum
End Sub

Thanks for all your help and patience

desmondno1

Desmond:

Here is some primitive code to try. I tested it and it works.

Code:

---

Option Explicit
Sub Macro1()

Dim Jobnum As String
Dim aRow As Range
Dim srcSheet As Worksheet
Dim dstSheet As Worksheet
Dim dstCell As Range
Dim found As Boolean      'Search Flag

'DUMMY JOBNUM FOR MY TEST - USE YOUR INPUT BOX VALUE
Jobnum = "R123"

'Initialize the Search flag to NOT found
found = False
'Set HANDLES for the Source and Destination Worksheets
Set srcSheet = ActiveSheet
Set dstSheet = Sheets("Invoiced Sales Report")

For Each aRow In srcSheet.UsedRange.Rows
    'TEST TEST TEST TEST
    Debug.Print aRow.Row, Jobnum
    'END TEST
    'Search for the User Entered Jobnum
    If Cells(aRow.Row, "A").Value = Jobnum Then
        'Here is where you operate on the identified row
        'Copy the row for this Jobnum
        aRow.Copy
        'Paste it onto the bottom of the destination sheet
        'This will NOT paste into Row 1 of an EMPTY SHEET!
        Set dstCell = dstSheet.Cells(dstSheet.UsedRange.Rows.Count + 1, "A")
        'Identify the Paste Cell ... Inserts a new row here.
        dstCell.PasteSpecial
        'Set the Search Flag to FOUND!
        found = True
        'Search is complete ... exit the loop
        Exit For
    End If
Next aRow

'Clean House
Set dstCell = Nothing
Set srcSheet = Nothing
Set dstSheet = Nothing

If found Then
    Exit Sub
Else
    MsgBox "JOB NUMBER ENTERED: " & Jobnum & " WAS NOT FOUND!"
End If

End Sub

---

Good Luck and Good Programming!

Thank you V much. lots to work on and get my head round.
You are a star