Posts by mikerickson

Re: Data Rotation

Your routine starts off with two sub statements without an end sub. Which won't work.

Is your wafer map the only thing on the sheet? That will make "locating" it for the routine much easier.

Re: Paste Special Values

Ahh.. so mine was the right answer in the wrong direction.
Yes, VB is about the only way to go on this.

Re: Range Comparison Formula

If you are looking to hightlite E2 when it is not equal to A5 then the conditional formatting formula in E2 should be =NOT(A2=E2). The same formula works in A2 to highlight that when they aren't equal.

If thats not the case, what do you mean by "compare both for differences"? What cells should be highlited under what circumstances?

Re: Paste Special Values

if you put =A1 in a cell, then that cell will contain the formula "=a1". It will show the same as A1, but it's content will be a formula not a value.

Spreadsheet formulas cannot put a value in another cell. They can only effect the contents of the cell they are in. You requested, "I want to put the value only of A1 (not the formula) in another worksheet. I want to do this via a formula only, not VBA." Excel worksheet formulas do not put; they get.

Re: Paste Special Values

You can't do it with worksheet formulas. If you enter a formula in a cell, that cell will contain a formula.

Re: Data Rotation

paste it in your module. Link your button to a routine like

Sub Button_Click
   ' your code to set the range variable RotateThisRange


   Call Rotate_From_Here(RotateThisRange)
End Sub

You will have to write your code to set RotateThisRange to include the legend column (on left) and row( on bottom)

Re: To Sum Cells Until Preset Value Is Reached

I was going to suggest that you change the if starttime inequality .. but its already set for that.

Is your list of times a list =A1+time(0,5,0) kind of structure. I've found that that within 12 hours that kind of incrimenting will cause round off error. Try to add

startTime=startTime-time(0,0,5)

at the start of the routine.

Re: Data Rotation

It wasn't clear what your input range was so...
Give Rotate_from_Here the range (including the legend column and row) as its argument.
It will seperate the left column and bottom row as legend values, rotate the remaining data, adjust the legends and put them in place.

I hope it helps.

Sub Rotate_from_Here(xRay As Range)
Dim vLegend As Range, hLegend As Range
Dim dataRay As Range, temp As Variant, i As Long
Dim vRRay As Variant, hRRay As Variant
Set vLegend = Range(xRay.Cells(1, 1), xRay(xRay.Rows.Count - 1, 1))
Set hLegend = Range(xRay.Cells(xRay.Rows.Count, 1), xRay.Cells(xRay.Rows.Count, xRay.Columns.Count))
Set dataRay = Range(xRay.Cells(1, 2), xRay.Cells(xRay.Rows.Count - 1, xRay.Columns.Count))
vRRay = Application.Transpose(vLegend.Value)
hRRay = Application.Transpose(Application.Transpose(hLegend.Value))
For i = 1 To Int(UBound(hRRay) / 2)
    temp = hRRay(i)
    hRRay(i) = hRRay(UBound(hRRay) + 1 - i)
    hRRay(UBound(hRRay) + 1 - i) = temp
Next i
Call rotate90counterclockwise(dataRay)
Range(xRay.Cells(1, 1), xRay.Cells(UBound(hRRay), 1)).Value = Application.Transpose(hRRay)
Range(xRay.Cells(UBound(hRRay), 2), xRay.Cells(UBound(hRRay), UBound(vRRay))) = vRRay
End Sub


Sub rotate90counterclockwise(inRay As Range)
Dim outRay As Range
Dim inDataRRay As Variant, outDataRRay
Dim i As Long, j As Long
Dim high1 As Long, high2 As Long
inDataRRay = inRay.Value
high1 = UBound(inDataRRay, 1)
high2 = UBound(inDataRRay, 2)
ReDim outDataRRay(1 To high2, 1 To high1)
For i = 1 To high1
    For j = 1 To high2
        outDataRRay(high2 - j + 1, i) = inDataRRay(i, j)
    Next j
Next i
Set outRay = inRay.Cells(1, 1)
Set outRay = Range(outRay, outRay.Cells(high2, high1))
outRay.Value = outDataRRay
End Sub

Re: Placement Of Forms Listbox

Thanks. Populating the list wasn't my main problem. Where on the screen? is the hard question.
I like the idea of hiding/unhiding the list. Creating it only once a session makes things easier.

Re: To Sum Cells Until Preset Value Is Reached

Change

Set aRay = Cells(Rows.Count, 1).End(xlUp) 
    Set aRay = Range(Range("a1"), aRay

to

Set aRay = Range("a7:a288")

Re: Setting Listbox Value Doesn't Set Listbox Value

Hmm.. inadvertant Design mode maybe?
Glad to hear its working.

Re: To Sum Cells Until Preset Value Is Reached

Quote from spope

It looks like this function is correctly returning the time value in the row in column A that is in the same row as the final summed number in column K. Is that right?

Yes

Quote

If so, then how do I return the value in the next row using xray.value?

If you want to modify the function to return the number below the one it currently does, change this.

untilKsums = xRay.Offset(1,0).Value

Re: To Sum Cells Until Preset Value Is Reached

OK change these lines

Set aRay = Range(Range("a7"), aRay) 


If xRay.Value >= startTime Then RunningSum = RunningSum + Val(xRay.Range("k1").Value)

What is the function doing that it shouldn't be. or not that it should?
Oops, just noticed that I left a test line in. remove the MsgBox line.

Re: Setting Listbox Value Doesn't Set Listbox Value

Just some quick thoughts.
Is the Listbox a multi-select. If so, .Value is unreliable. You might try

myValue=myListbox.List(0)

Is the Userform loaded when you run the first section of code? That may be why you have trouble getting the right value for myVal.
Is "Counter 1" already in the list when you run code snipet #2? If not,

myListbox.AddItem "Counter 1"
myListbox.Selected(mylistbox.ListCount-1)=True.

is what you need, not setting .Value

I've gotten odd responce from the listbox On_Click event. The MouseDown event is similar, but I put most of my routines in the Change event.

Re: Formula If, But Not...

Impossible. Your question asked for a formula that will return 10 if 0<a1 and 25 if 0<a1<100. No value can be both 10 and 25.

I suspect that you got an inequality backwards somewhere. By the way, what do you want it to return if a1=0 and if a1>=100?

Re: To Sum Cells Until Preset Value Is Reached

This custom function will return the time (from columnA) that you want. Enter the arguments startTime and targetSum. If targetSum is not reached, it returns #NUM error.

Function untilKsums(startTime As Double, targetSum As Double)


Dim RunningSum As Double
Dim aRay As Range, xRay As Range


Set aRay = Cells(Rows.Count, 1).End(xlUp)
Set aRay = Range(Range("a1"), aRay)
For Each xRay In aRay
    If xRay.Value >= startTime Then RunningSum = RunningSum + xRay.Range("k1").Value
    If RunningSum >= targetSum Then Exit For
    MsgBox RunningSum
Next xRay
If RunningSum < targetSum Then untilKsums = CVErr(xlErrNum): Exit Function
untilKsums = xRay.Value


End Function

I have a list of People on one sheet and a list of Shifts on another.
Some people have Qualifications (CPR, electirician, Non-Violence trained, etc.) and some shifts have Requirements.

I would like to provide a (non-required) validation list for these entering these values. If the user enters a new requirement or qualification.

An explicit Validation list is too long. I don't want to use cells to hold a list that Validation can read from.

So, my current plan is to a Selection_Change event to create/destroy a form ListBox, which will be populated by my VB code. How to place that is presenting some challanges. How to turn the user's multiply selected input into a comma delimited string is the easier of my two main challanges.

So, my concice question is when the user selects a single cell, how do I create a listbox that is possitioned over that selected cell?

My non-concice question is: Is there a better way to go about this?

Thanks.

PS Is there a way to get the thread title out of Title Case? Its anoying that I can't write what I want.

Re: Last Cell In Column To Inputbox

If you set an application.inputbox to type:=8, the user can use a mouse to select the cell he wants.

Re: Run Macro From A Removable Usb Flash Drive

You could put a workbook on the flash drive, put your macros in that workbook. You'd have to have the workbook open for the macros to work.

What are you trying to keep secure. Excel is no good for secrets. If security is very important to you, you should probably use a different program.