Posts by MaximS

Re: Ozgrid Fantasy Football

yeap, but I've already used my wild card as 5 layers were off

Re: Google Chrome. New Web Browser

Confirmed no adblocker. Just started testing and for now I am nicely suprised. It is a lot faster than FF so far. All my websites working perfectly fine

Re: Google Chrome. New Web Browser

This is looking interesting and prommising.

Re: Ozgrid Fantasy Football

That was worst gameweek ever

Re: Merge Duplicate/Similiar Rows Keeping Data In Same Columns

I've added small user form for your convinience. You can set up start end the end of your range. See attached file for details.

Re: Merge Duplicate/Similiar Rows Keeping Data In Same Columns

Or you can try my way. See attachement for details. Run the macro called solution located in module 2.

I'v tested on full and halfs and it worked.

Re: Developing A Formula

I have tailored my idea to your layout and needs. Please see attachement for details.

Re: Copy Non Blank Cells & Paste As Values To Another Range

It is taking a bit because of your code which is run every time with it.

I didn't want to mess with your code as you know better what is neccessary for your task.

I hope I helped a little.

Re: Developing A Formula

Here is simple User Defined Formula:

To use it:

1. Open up a new workbook.
2. Get into VBA (Press Alt+F11)
3. Insert a new module (Insert > Module)
4. Copy and Paste the Excel user defined function examples -
5. Get out of VBA (Press Alt+Q)
6. Use the functions (They will appear in the Paste Function dialog box, Shift+F3, under the "User Defined" category)

Function POINTS(Position As Range, Member As Range, Karts As Range)
If Member = "Yes" Then
    Select Case Position
        Case 1
            x = 200
        Case 2
            x = 175
        Case 3
            x = 155
        Case 4
            x = 140
        Case 5
            x = 130
        Case 6
            x = 120
        Case 7
            x = 110
        Case 8
            x = 100
        Case 9
            x = 90
        Case 10
            x = 80
    End Select
 
    POINTS = x + Karts
 
    Else
    
    POINTS = ""


End If
End Function

If you need any help just ask

I've attached the file witch few validation lists to prevent errors.

Re: Copy Non Blank Cells & Paste As Values To Another Range

To avoid empty rows to be copied you need to sort (descending order) range which needs to be copied up. Added coding in Module 8.

I put some of your code form sheet PS-9 in comments as it was making this problem with 0:01 time values.

You can now remove some of your modules which won't be used.

See attachement for details.

Re: Special Paste Between 2 Ranges Or Skip Hidden Rows

Hi try this code:

Private Sub solution()


 Sheets("PS-9").Select


y = 120
For x = 10 To 29


If IsEmpty(Cells(x, 2).Value) Then
For z = 2 To 11
Cells(x, z).Value = Cells(y, z).Value
Next
y = y + 1
End If
Next


For x = 44 To 63


If IsEmpty(Cells(x, 2).Value) Then
For z = 2 To 11
Cells(x, z).Value = Cells(y, z).Value
Next
y = y + 1
End If
Next


For x = 78 To 29


If IsEmpty(Cells(x, 2).Value) Then
For z = 2 To 11
Cells(x, z).Value = Cells(y, z).Value
Next
y = y + 1
End If
Next


End Sub

insert into new module and attach to the command button.

You can add extra code to delete copied cells if you like.

I hope this will help.

Re: Lower Memory Usage

Hi check below link for information about how memory work in Excel with saving solutions.

http://www4.ableowl.com/ableowl/pdf/Indepth_Excel/Mag118_sample.pdf

I think saving your file periodicaly should free up memory and reduce memory consumption.

Re: Special Paste Between 2 Ranges Or Skip Hidden Rows

Hi,

Before we start can you answer following questions:

1. Can Range B120:K239 be sorted in asc/desc order first or it needs to go as it is now?
2. Are you realy want to copy/paste content of columns F to K as you have got validation list in destination cells? (This will create circullar references)

Re: Ozgrid Fantasy Football

I'm in hope to see the rest there.

Re: Exel/vba Jobs

I'm not saying I'm going to do anything on my own (just not got use to). I'm rather thinking about learning as much as I can now to build-up good workshop and confidence. Then will try to change a job for somethng closer to programming.

Re: Convert Cell List To Range

without VBA you can use formula:

="{"&C2&";"&C3&";"&C4&"}"

but you have to add each cell manually

Re: Create Copy Of Sheet For Each Name In List

Why not to sort first your list in any order that should leave blank ones at the bottom.

Re: Transpose Every Other Row To Column

Use below code should work regardless number of rows you have unless there is nothing else what you stated.

Range("B1").FormulaR1C1 = "% Change"
    
    Dim LastRow, rCount As Long
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    
    Dim x As Integer
    
    rCount = LastRow - 1
    
    For x = 2 To rCount Step 2
    Cells(x, 2).Value = Cells(x + 1, 2).Text
    Next x
    
    For r = 3 To (LastRow + 3) / 2 Step 1
    Application.Rows(r).EntireRow.Delete
    Next r

Re: Exel/vba Jobs

Apart from what I stated before (Msc in e-business which included databases, data management systems, bit of programing in VB and C#, web sites and of course projecting) I'v done my BBA in Management and Marketing and finished Economical College (spec. Banking).

Most of my jobs were not connected to what I'd been learning in my past with small exemptions (bank trainee 1 yr. - couldn't stay up; warehouseman @ PC commponents wholesaller 3.5 yr. - nice konwledege about what inside of pc, helped me a lot to pay for studies; book keeping for about 3 yrs. - just for fammily; outdoor representative 6mths - negotiating with big guys on sport events, seasonal job; 6mths postman - studies again; warehouse 1 yr; warehouse 1yr.; stock controller 1yr. It might look funny but I learnt a lot about how businesses are run while working at warehouses - every time different type of company (fashion, computer parts, multimedia) and different type of database system.

Finaly I can do at work whatever I want - having plenty time to practice Excel and VBA.
It is like refreshing already gained knowledge plus exploring fields I' ve never knew they exist.
More I learn I feel like being at the bottom of the mountain I'd like to challenge.

For some reasons I found it hard to learn at home (little baby playing around, wife want something, need of relax, etc.), but at work I can sit and fight with macros for hours without having a brake.

Re: Copy Over Information

sample file will help to solve the problem quicker