Direction and advice on VBA project

[harperjo]

I have a project that requires me to do some looping or {cursor} on a worksheet. [I have attached a mock up of this] I am not completely certain on how to proceed with this......if it was a database I would do a select and cursor the selections but I am not that comfortable with VB. Let me try to explain what I am needing to do and perhaps you gurus can suggest a direction or block of code I can build upon.

rawdata worksheet can contain thousands of records..........
first action copy rows from A:D to a new worksheet (Formated Data) starting at A2
Select all rows that contain a value in column E (rawdata)
Copy values A:D and paste at the bottom of Formated Data worksheet
Add the the E1 header value as the value for Formated Data column E all new rows.
Add the corresponding value from the Rawdata column E to the classvalue column in the formated data sheet.....


This must happen for 180+ attribute values.......some may be filled in some not. So I have to cusor through the rows that have date fill them in and go on to the next column select those values and do it again..........

At the end I will resort by the item number so the data will be grouped by the item number.........

I am just not sure how to accomplish the cursor.....loop and past portion.

any ideas anyone............

I hope I explained this well enough..........and in the right section.

 

[dourpil]

Hi!
What you're trying to do isn't that complicated. I'll give you a few hints that'll get you started.

If you don't manage to write a working code I'll get back to you but you should be alright


You say you can have a lot of records. We thus first want to know the amount of rows where there's data.
I use this line in my files:

n = Thisworkbook.Worksheets("rawdata").Cells.Columns(1).Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row

so now we know we want to loop from row 2 to row n. Then it's just a matter of passing the cell values:

formattedindex = 1
for x = 2 to n
  for y = 5 to 10
    if not rawdata.Cells(x,y) = "" then 'if attr cell isn't empty, copy data
      formateddata.Cells(formattedindex,1) = rawdata.cells(x,1)  'repeat this line for item, description, serialnum, bin and y which is your attr
    formattedindex = formattedindex + 1
end if
next y
next x

 

[harperjo]

Thank you sir.........I will let you know how it comes out.

 

[dourpil]

So how did it go?

 

[harperjo]

Well thank you for helping out and pointing me in the right direction...........very helpful

Sorry for not getting back sooner by I was very engrossed in another nightmare......

this is the final code I was able to get created.......pretty slick.

Sub TEST()
Dim mySheet As Worksheet, myOtherSheet As Worksheet, myBook As Workbook 'Define your workbooks and worksheets as variables
Set myBook = Excel.ActiveWorkbook
Set mySheet = myBook.Sheets("rawdata")
Set myOtherSheet = myBook.Sheets("Sheet2")

Dim formattedindex As Integer, x As Integer, y As Integer, n As Integer

formattedindex = 1
n = 5


For x = 2 To n
  For y = 5 To 10
 If mySheet.Cells(x, y).Value <> "" Then
       myOtherSheet.Cells(formattedindex, 1).Value = mySheet.Cells(x, 1).Value
       myOtherSheet.Cells(formattedindex, 2).Value = mySheet.Cells(x, 2).Value
       myOtherSheet.Cells(formattedindex, 3).Value = mySheet.Cells(x, 3).Value
       myOtherSheet.Cells(formattedindex, 4).Value = mySheet.Cells(x, 4).Value
       myOtherSheet.Cells(formattedindex, 5).Value = mySheet.Cells(1, y).Value
       myOtherSheet.Cells(formattedindex, 6).Value = mySheet.Cells(x, y).Value
    formattedindex = formattedindex + 1
  End If

Next y
Next x

End Sub