Posts by HaHoBe

Re: VBA runtime error '6': Overflow

Hi, snooks679,

instead of using Integer try using Long:

Dim row As Long
Dim LastRow As Long
'...
Private Function SetLastRow(WorksheetName As String) As Long

Ciao,
Holger

Re: trying to activate sheet based on cell value

Hi, rvkcools,

assign a value to tempname otherwise the variable will show ab emnpty sting which can´t be found to to contents in the cells compared.

Maybe try it like this

Sub emptest()
Dim tempname As String
Dim lngCounter As Long
tempname = "test"
With Worksheets("team")
  For lngCounter = 24 To .Cells(Rows.Count, "A").End(xlUp).Row
    If .Cells(lngCounter, "A").Value = tempname Then
      Worksheets(tempname).Select
      Exit For
    Else
      MsgBox "no sheet " & .Cells(lngCounter, "A").Value
    End If
  Next lngCounter
End With
End Sub

Ciao,
Holger

Re: Keep a running total in a single cell

Hi, basshunter514,

in case of a formula you must either relate in the cells in which the entries are made directly or to the Calculate-event to catch if any new calculation in a range has been made. This might require a volatile function in the sheet as well.

Ciao,
Holger

Re: Correct sytax to resize cells

Hi, goneps,

maybe you give this a try:

Dim lngResRow As Long


For lngResRow = 2 To 4
  Range("A3").Resize(lngResRow, Columns.Count).Select
Next lngResRow

Range("A3").EntireRow.Resize(2, Columns.Count).Select

The Select is just for showing the effects on that.

Ciao,
Holger

Re: Set print area based on a cell value with vba

Hi, mikejaco,

some parts of your code must have gone while copying.

I can´t figure out a reason to start at the top for the loop (I´d preferred to start from the bottom up as the last will overwrite any existing pagesetup). Code may be applied to the Workbook_BeforePrint(Cancel As Boolean) event in ThisWorkbook and may be altered to reflect a certain sheet or find out the last used row on that sheet:

Dim lngCounter As Long
For lngCounter = 0 To 10
  If Cells(23 + lngCounter * 25, "A").Value > 0 Then
      ActiveSheet.PageSetup.PrintArea = Range(Cells(1 + lngCounter * 25, "A"), Cells((lngCounter + 1) * 25, "K")).Address
  End If
Next lngCounter

Dim lngCounter As Long
For lngCounter = 23 To 223 Step 25
  If Cells(lngCounter, "A").Value > 0 Then
      ActiveSheet.PageSetup.PrintArea = Range(Cells(lngCounter - 22, "A"), Cells(lngCounter + 2, "K")).Address
  End If
Next lngCounter

Ciao,
Holger

Re: Ranges In Vba

Hi, marino,

Set rng1 = Range(Cells(21, 7), Cells(40, 7))


Cells(21, 8) = "=PERCENTRANK(" & rng1.Address & "," & Cells(21, 7).Address & ")"

Ciao,
Holger

Re: Is there a way to create code that will insert a formula within a spreadsheet?

Hi, legaltrends,

you could use the Worksheet_Change-Event of the worksheet and limit it to just column C. If anything is entered into a cell in column C the following code is triggered (sorry, didn´t get your formula in G to work - this part of the code is missing here):

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Columns("C:C")) Is Nothing Then Exit Sub
Application.EnableEvents = False
With Target
  .Offset(0, 1).Value = Left(Target.Value, 1)
  .Offset(0, 2).Value = Mid(Target.Value, 2, 1)
  .Offset(0, 3).Value = Mid(Target.Value, 3, 1)
End With
Application.EnableEvents = True
End Sub

No formulas should make up a bit of speed...

Ciao,
Holger

Re: Show/Hide toolbars for current workbook only

Hi, GuyGadois,

you may have a good look at ThisWorkbook and the Private Sub Workbook_WindowDeactivate(ByVal Wn As Excel.Window) and Private Sub Workbook_WindowActivate(ByVal Wn As Excel.Window) events to call your codes when changing workbooks.

Ciao,
Holger

Re: get file\properties\autor & otganizastion by vba

Hi, DROM,

you should look for the BuiltinDocumentProperties of the workbook.

Option Explicit


Sub BuiltinDocumentProperties()
Dim intI As Integer
Dim obj As Object
intI = 1
Sheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "DocumentProperties-" & Format(Date, "YYMMDD")
For Each obj In ActiveWorkbook.BuiltinDocumentProperties
 Cells(intI, 1).Value = obj.Name
 On Error Resume Next
 Cells(intI, 2).Value = obj.Value
 intI = intI + 1
Next obj
Columns("A:B").EntireColumn.AutoFit
End Sub

Code will add a new worksheet at the end of the workbook, change the name, amend the date and list the properties where filled in.

Ciao,
Holger