VBA Help

[wildest_jjk]

Hi guys,

I'm trying to write my first VBA script as failing miserably. Essentially I am trying to put a sheet of data in various groups depending on some values in the cells.

I guess the first question would be is there an easier way of doing this?

If not then what is the easiest way to find the last row on the sheet? I see a lot of suggestions using End(xlUp) or something like that but I cannot seem to get this to work!

Thanks

 

[dazzerd]

lots of ways none of them perfect but the best option imo is:

range.find("*")

have a look here for a decent explanation how it works.

http://www.rondebruin.nl/win/s9/win005.htm

Best to create a wrapper function and pass the range you want to find the lastrow for as a parameter:

i.e.

Public Function MyLastRow(byVal rangeToSearch as range) as long

Dim result as long

om error goto eH

result = rangeToSearch.FInd("*"....more parameters)

MyLastRow = result
on error goto 0
exit function

eh:

''error handling here
End Function

 

[dazzerd]

As a tip you can also loop for all the sheets in your workbook like this


Public Sub MyLoop()

Dim ws as worksheet

For each ws in thisworkbook.sheets

''do whatever you need to do here to group them

Next ws



End Sub

 

[wildest_jjk]

Thanks for that.

Does anyone know if there is a simpler way of grouping large data based on some criteria?

I'm really struggling with the VBA...

 

[wildest_jjk]

Ok I'm making progress

Could someone tell me if this is syntactically correct please?

If Cells(5, k).Value = "" Or Cells(5, k).Value + total < 75 Then

 

[wildest_jjk]

k = integer variable
total = integer variable