Posts by little_lobster

Re: Activate Screensaver remotely

as far as I know I posted the topic in VBA/Macro, and so I was trying to ask if there is a way/code to connect and activate the screensaver.
Ever heard also of vba code that could launch programs like vnc from excel using VBA?

Re: Update Report date but will not auto-update after

Just an update on this topic I started:

I did not use any formula at all since it will confuse the people who will open the attachment. I just edit the date everyday.

Grimes0332, thanks. I decided to not put any formula, the warning message in case of circular formulas might annoy the readers especially those who are not tech savvy when it comes to Excel.

Hi guys,

Would like to know if there is a way to activate screensaver remotely to different PCs?

Right now I only have a batch file script that copies the picture to the slideshow folder in different PCs and then I activate it (screensaver that will show my newly saved pictures) manually using VNC. I have hundreds of them and doing it manually is kind of time consuming.

Thanks in advance.

Re: USD100 : Automated Roster

^^Kjbox, i have been sending you PMs and emails. Sent you the payment a week ago but I have not gotten any reply from you. I saw that you posted in a thread here an hour ago. Please reply to my message as I need the solution urgently. Thanks.

Re: Update Report date but will not auto-update after

Thank you, Mohammed for the quick reply. Looks like I will have to revise my entire report.

Hi,

Just wondering if it is possible to have the date, (like for report date) to not update after sending the excel workbook by email? As much as possible, I want to stay away from using VBA for this. Is it doable?

I currently have my formula:

=TEXT(NOW()-1,"dd-mmm-yyyy")

Thanks in advance.

Re: USD100 : Automated Rota for scheduling staff

Hi kjbox, sent you a PM. Thanks

Re: USD100 : Automated Rota for scheduling staff

Êmail sent. Thank you.

Payment already sent to Ozgrid's paypal.

Hi,

I would like to create an automated roster for a small group of employees. I will send the sample file to anyone who would like take this up.

Thank you.

[TABLE="width: 671"]

Summary:

3 Sr. Staff

5 Jr. staff

1 staff on Permanent LT task

Total of 5 Different Tasks:

DM

EM

LM

ET

LT

[/TABLE]

[TABLE="width: 671"]

What I need basically are as follows:

1. Rotation will be every 2 weeks, 2 weeks of EM/ET then LM/LT (If staff is on ET or EM then next two weeks should be LT or LM and vice versa) except for staff#6 which will be permanently doing LT. Everybody will be doing 4 days on 3 days off except for DM that will have 5 days on 2 days off

2. Only Sr staff can be rostered in DM from Sunday to Thursday (total of 5 days a week if doing DM)

3. There should be at least 1 staff for EM, ET, LM, LT everyday and 1 DM from Sun to Thu

4. Only one staff is allowed to take vacation leave per day

5. In case of changes that is urgently needed for some activities (e.g. testing), roster can be amended but criteria above should be met (a pop up message can be generated to warn the user that certain criteria will not be met in case-- an OT can be suggested here)-- the most important thing is that there is still enough manpower for EM, ET, LM and LT everyday

[/TABLE]

Re: Populate Dates in Cell Range

thank you cytop. I think I have found an answer form other thread from jindon:

Sub test() 
    Dim x 
    x = DateDiff("d", [a1], [b1]) + 1 
    With Range("c1") 
        [a1].Copy .Cells 
        .AutoFill .Resize(, x) 
    End With 
End Sub

Okay, first, I want to apologize, may be this is a bit stupid question to ask but I tried searching for sample codes but all are for populating rows liks A1 to A28. This is my first time using VBA again after 2 years so you can guess that I am a bit slow again in doing something in VBA. So there, what I would like to do is simply populate Range say B5 to AC5 with dates according to the cells where I will be putting the start and end date.

Thank you so much in advance.

Re: VBA: Looping through different Columns

pike: It gives each single cell inside the range instead of picking up the range (e.g. "A:A") But thanks. Really appreciate it.

I now have to work on making my code run faster.
Thanks to all!

Re: VBA: Looping through different Columns

holycow, thank you so much! your code worked perfectly as I only have static sets of columns (I am not sure though what contiguous column means).

apo: I will also try your code, I am still trying to improve my code as it is as slow as snail. I want to loop through whole range because I need to run my code inside that range. It's like I have this set of codes that will run through that set of range. Or did I just make it more vague?

Btw, I used

Set rzRng = SSI.Range("AM:AM, AS:AS, AT:AT, BU:BU")

instead of using using "Union", is there a difference if I use Union?

Thank you so much.

====EDIT====
apo: I tried your code, it worked too, thank you so much.

I am trying to code something that will loop through a given set of columns. I know that if I say "For each C in R", that would mean, looping through each cell inside the Range "R", but what I want is something like this:

Dim rzRng as range, rzSmallRng as range
Set rzRng = Union(range("A:A"), range("C:C"), range("F:F"), range("AB:AB"), range("AS:AS"))


For Each rzSmallRng in rzRng
           statement here...
next

(I have a total of 30 columns that I need to put inside that Union function)
And then loop through the range as a whole set (not though each cell of that range).

I also tried the following code:

With Union(range("A:A"), range("C:C"), range("F:F"), range("AB:AB"), range("AS:AS"))
        statement here....
    End With

But it ended up executing the code in Range"A:A" only.

Re: VBA: Run codes to specific Columns

Thanks Patel. I feel stupeed now, I never thought it was that easy. Thank you so much!

Hi, I am here again to ask for your kind help . I have a code that generates named range (Thanks to mikerickson) and automatically puts
random scores on each cell in the range. Is there a way I could run the same code to specific columns in a sheet?
For example:

Col A Col B Col C
WNP WNP
WNP WNP
WNP WNP
WNP WNP
MNL MNL
MNL MNL
MNL MNL
MNL MNL
CEB CEB
CEB CEB
CEB CEB

Since I have the same exact contents in Column C, I will use the same code that I used in Column A.

I have the following Code for Column A as:

Sub test()
    Dim dataRange As Range, oneCell As Range
    With Range("A:A")
        Set dataRange = Range(.Cells(1, 1), .Cells(.Rows.Count, 1).End(xlUp))
    End With
    For Each oneCell In dataRange
        TermRange(oneCell, oneCell.Text).Name = oneCell.Text
    Next oneCell
Call Q19
End Sub
 
Function TermRange(columnOfInterest As Range, searchTerm As String) As Range
    Dim firstFoundCell As Range, lastFoundCell As Range
    With columnOfInterest.Columns(1).EntireColumn
        Set lastFoundCell = .Find(searchTerm, after:=.Cells(1, 1), searchdirection:=xlPrevious)
        Set firstFoundCell = .Find(searchTerm, after:=lastFoundCell, searchdirection:=xlNext)
    End With
    Set TermRange = Range(firstFoundCell, lastFoundCell)
End Function


Function Q19()
'This populates cells in the given range with numbers 5, 4, 3 and 2 with a given % or number of cells to be populated with each
Dim rng As Range
Dim count5 As Integer, count4 As Integer, count3 As Integer, count2 As Integer
Dim randCell As Range, TotalWNP As Integer, Total5 As Integer, Total4 As Integer, Total3 As Integer, Total2 As Integer


TotalWNP = WorksheetFunction.CountA(Range("WNP"))
Set rng = Range("WNP")
rng.Value = "6" 'Set all cells = 6.'
Total5 = 15
Do


    Set randCell = rng.Cells(Application.WorksheetFunction.RandBetween(1, TotalWNP))
    'make sure we're not overwriting another 5-value'
    If Not randCell.Value = 5 Then
        randCell.Value = 5
        'increment the counter variable'
        count5 = Application.WorksheetFunction.CountIf(rng, "5")
    End If
Loop While count5 < Total5  'exit the loop once Total5 cells have been changed to 5.'
'Repeating all the steps for the same range of cells
Total4 = 9
Do


    Set randCell = rng.Cells(Application.WorksheetFunction.RandBetween(1, TotalWNP))
    If randCell.Value = 6 Then
        randCell.Value = 4
        count4 = Application.WorksheetFunction.CountIf(rng, "4")
    End If
Loop While count4 < Total4
Total3 = 8
Do


    Set randCell = rng.Cells(Application.WorksheetFunction.RandBetween(1, TotalWNP))
    If randCell.Value = 6 Then
        randCell.Value = 3
        count3 = Application.WorksheetFunction.CountIf(rng, "3")
    End If
Loop While count3 < Total3
Total2 = 2
Do


    Set randCell = rng.Cells(Application.WorksheetFunction.RandBetween(1, TotalWNP))
    If randCell.Value = 6 Then
        randCell.Value = 2
        count2 = Application.WorksheetFunction.CountIf(rng, "2")
    End If
Loop While count2 < Total2
End Function

Thanks in advance!

Re: [RESOLVED] Naming Range based on Cell contents

mikerickson

This code rocks! Works like a charm! Thank you so much!

Hi, I am trying to code something that is similar to the code here http://www.ozgrid.com/forum/showthread.php?t=158228
The thing is that on my column A, what I have is the following:
Col A
MNL
MNL
MNL
CEB
CEB
CEB
CEB
WNP
WNP
WNP

I want to name the range of cells in Col A that contains "MNL" with "RngMNL", "CEB" with "RngCEB", "WNP" with "RngWNP" or something like that.

Thanks in advance!