Posts by tinyjack

Re: Validated Cell

Well, you could use send keys.

[vba]
[a1].Select
SendKeys "%{down}"
[/vba]

TJ

Re: Validated Cell

I usually do what you are trying to do with a ComboBox on the sheet, as attached:

TJ

Re: large combination from small set

To see the results from the code you need to go into the VBE and look at the immediate window, which is where Debug.Print outputs to.

With regards to the second part of your post, try puting the following code into an empty workbook and then running Main(). This is just some code I knocked up and as such I am sure there is a lot that could be done to improve it's efficency.

ps Balls = nucleotides and Cups = four constants

[vba]
Option Explicit

Dim lngRow As Long

Sub Main()

Dim lngCups As Long
Dim lngBalls As Long

Application.ScreenUpdating = False

Sheet1.Rows("1:60000").Delete

lngBalls = 20
lngCups = 4

lngRow = 1

If doit(lngBalls, lngCups) < 60000 Then
Details lngBalls, lngCups
End If

Application.ScreenUpdating = True

End Sub

Public Function doit(ByVal lngBalls As Long, ByVal lngCups As Long) As Long

Dim lngloop As Long

If lngCups = 1 Or lngBalls = 0 Then
doit = 1
Else
For lngloop = 0 To lngBalls
doit = doit + doit(lngloop, lngCups - 1)
Next
End If

End Function

Sub Details(ByVal lngBalls As Long, ByVal lngCups As Long, Optional ByVal strAnswer As String = "")

Dim strTemp As String
Dim lngloop As Long

If lngCups = 1 Then
If Len(strAnswer) > 0 Then
strTemp = strAnswer & ";" & CStr(lngBalls)
Else
strTemp = CStr(lngBalls)
End If
OutputAnswer strTemp
ElseIf lngBalls = 0 Then
strTemp = strAnswer
For lngloop = 1 To lngCups
If Len(strTemp) > 0 Then
strTemp = strTemp & ";0"
Else
strTemp = "0"
End If
Next
OutputAnswer strTemp
Else
For lngloop = 0 To lngBalls
If Len(strAnswer) > 0 Then
strTemp = strAnswer & ";" & CStr(lngBalls - lngloop)
Else
strTemp = CStr(lngBalls - lngloop)
End If
Details lngloop, lngCups - 1, strTemp
Next
End If

End Sub

Sub OutputAnswer(ByVal strAnswer As String)

Dim varAnswers As Variant
Dim lngloop As Long

varAnswers = Split(strAnswer, ";")

For lngloop = 0 To UBound(varAnswers)
Sheet1.Cells(lngRow, lngloop + 1).Value = varAnswers(lngloop)
Next

lngRow = lngRow + 1

End Sub
[/vba]

HTH

TJ

Re: Slow Code

Just work with the object directly, so

[vba]
Range("F19").Select
Selection.Copy
Range("D9").Select
ActiveSheet.Paste

'Becomes

Range("F19").Copy Destination:=Range("D9")
[/vba]

If you post a small section of your code, we can have a look and see what you can do.

TJ

Re: Slow Code

Looking in my crystal ball, I would say do not use .Select

TJ

Re: Remove all spaces from String

In xl97 you might nned to do something like:

[vba]
Dim strOut as String
Dim lngLoop as Long

For lngLoop = 1 to Len(strString)
If Mid(strString, lngLoop, 1) <> " " Then
StrOut = StrOut & Mid(strString, lngLoop, 1)
End If
Next
[/vba]

TJ

ps This is based on Replace not being available to xl97 (if my memory is correct).
If Instr is available you could do something based on that, with a loop.

Re: Data table alignment

Would data labels be an option rather than a data table?

Re: Data table alignment

Are you talking about the data on the spreadsheet not lining up with the columns of the chart?

If so have you thought about using the Data Table option inside the chart itself?

TJ

Re: large combination from small set

You can use a recursive procedure, soemthing like:

[vba]
Sub Wrapper()

Dim strLetters As String
Dim lngN As Long

strLetters = "atcg"

lngN = 5

Inner lngN, strLetters

End Sub

Sub Inner(ByVal lngN As Long, ByVal strLetters As String, Optional ByVal strAnswer As String = "")

Dim lngLoop As Long
Dim strTemp As String

If lngN = 0 Then
Debug.Print strAnswer 'or whatever you want to do with it
Else
For lngLoop = 1 To Len(strLetters)
strTemp = strAnswer & Mid(strLetters, lngLoop, 1)
Inner lngN - 1, strLetters, strTemp
Next
End If

End Sub
[/vba]

HTH

TJ

Re: put form in background when not clicked on

Have a look at the help files for the Show method and the modal option vbModeless.

TJ

Re: Delete charts on worksheet

You can use the ChartObjects collection:

[vba]
Sub DeleteEmbeddedCharts()

Dim wsItem As Worksheet
Dim chtObj As ChartObject

For Each wsItem In ThisWorkbook.Worksheets

For Each chtObj In wsItem.ChartObjects

chtObj.Delete

Next

Next

End Sub
[/vba]

Remember this will delete ALL the embedded charts.

TJ

Re: Non-numeric values in Charts

BBB appears twice in your example data, how would this be handled?

TJ

Re: Non-numeric values in Charts

You can put text values on the x-axis with no problems, have you tried it yet?

TJ

Re: Date formula

=today()

Tj

Re: Error bar and axis formatting in graphs

Firstly, I do not believe you can.

Secondly, Format Axis - Patterns - Tick mark labels - Low

HTH

TJ

Re: Range on inactive workbook

I am sure someone can give you a link to the technical reason why you cannot do this, but I just know you cannot. You have to do it in 2 steps:

[vba]
Workbooks("Automatic Daily Top Ten Report.xls").Worksheets("Title").Select
Range("FY1").Select
[/vba]

However, it is very rare that you ever need to use .Select, since you could do:

[vba]
Workbooks("Automatic Daily Top Ten Report.xls").Worksheets("Title").Range("FY1").Value = 27
[/vba]

without the need to select the range first.

or maybe even neater:

[vba]
Dim wbReport As Workbook
Dim wsReportTitle As Worksheet

Set wbReport = Workbooks("Automatic Daily Top Ten Report.xls")
Set wsReportTitle = wbReport.Worksheets("Title")

With wsReportTitle
.Range("FY1").Value = 27
End With
[/vba]

HTH

TJ

Re: Debug Is Like variable

LIKE is case sensitive unless OPTION COMPARE TEXT is used, could this be your problem?

If so I would:

[vba]
BookName = UCase(ActiveWorkbook.Name)

Islike1 = BookName Like "*GARBAN*" Or BookName Like "*STERLING*" _
'etc, etc
[/vba]

HTH

TJ

Re: Find cell that a function is called from

You could use Application.Caller:

[vba]
Function example()

Dim rngWhere As Range
Set rngWhere = Application.Caller

example = rngWhere.Row

End Function
[/vba]

But, I would pass the value to the function as it should give you more flexibility.

TJ

Re: Code to Load a Different File name each week.

[vba]
Dim strFile As String

strFile = "F:\Users\StockWatch\Data\" _
& Format(Worksheets("Sheet1").Range("A1").Value, "yyyymd") _
& "mp_whsStockWatch.xls"

Debug.Print strFile
[/vba]

HTH

TJ

Re: shortening this macro line

Have a look at Intersect:

[vba]
Dim rngTest As Range
With Worksheets("Sheet1") 'or whatever sheet
Set rngTest = Intersect(Target, .Range("D5:D53"))
End With

If Not rngTest Is Nothing Then
Msgbox "Target in right area"
End If
[/vba]

HTH

TJ