Posts by pdrobinson

Hi, thanks

Nearly there. I tried this and it seems to be failing at the end of the Indirect calculation. I had to add in an extra Oct[Dates] as the rest of the sumif references broke.

=SUMIFS(INDIRECT("Oct["&[@Server]&"]),Oct[Dates]"),Oct[Dates],">="&[From],Oct[Dates],"<="&[To])

attached Capture 1 shows it is finding the reference but then it fails to #REF on next step (capture 2)

[ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1210531}[/ATTACH]

[ATTACH=JSON]{"data-align":"none","data-size":"medium","data-attachmentid":1210532}[/ATTACH]

any thoughts?

Hi,

I have a data dump which has Servers (AMEWEW1ECCS01 being one) as headers and dates in column A. Then values are populated throughout the table for each date in each server.

This Table is named Oct and each column name is the server name, as in Oct[AMWEW1ECCS01]

I need to sum all values in the Oct table that are between a date range and for a specific server. This date range is defined by the [To] & [From] columns in another Table. The below works perfectly if I manually tell it which server it is.

=SUMIFS(Oct[AMWEW1ECCS01],Oct[Dates],">="&[From],Oct[Dates],"<="&[To])

Now I need to make the formula look at a cell in a column called [Server] and populate the Sum_Range accordingly. So replacing the red bit below to reference whatever is in the [Server] column. Any thoughts

=SUMIFS(Oct[AMWEW1ECCS01],Oct[Dates],">="&[From],Oct[Dates],"<="&[To])

Thank you for your help. Thought I would close this as I finally have it working.

Sub InsertARow()
Dim AR As Long
Dim MaxID As Long
Dim NewID As Long
Dim Tracker As Worksheet
Dim IDColumn As Range


Set Tracker = ThisWorkbook.Sheets("Tracker")
Set IDColumn = Range(Cells(14, 1), Cells(110, 1))


MaxID = Application.WorksheetFunction.Max(IDColumn)
NewID = MaxID + 1
AR = ActiveCell.Row


On Error GoTo ErrHandler
' Unprotect the sheet
    Tracker.Unprotect Password:="123"
' Insert a row
    Range("N" & AR).EntireRow.Insert
' Fill down the formula from above the inserted row to below it
    Range("N" & (AR - 1) & ":N" & (AR + 1)).FillDown
' Select ID cell for new row
    Cells(ActiveCell.Row, 1).Select
' Format ID Column
    ActiveCell.NumberFormat = "000"
' insert new ID number
    ActiveCell.Value = NewID
    
ExitHandler:
' Protect the sheet again
    Tracker.Protect Password:="123"
' Get out
    Exit Sub


ErrHandler:
    ' Report the error to the user
    MsgBox Err.Description, vbExclamation
    ' Always go past the exit handler section
    Resume ExitHandler
End Sub

Hi, thanks very much. I think I inserted it correctly below. I have reset the A column to be Number format for now. Unfortunately when I run it I get the new row, but followed by an error alert saying Overflow.

(Also, how does everyone paste their code in such a presentable manner, when I paste below it looks really messy lol)

Sub InsertARow()
Dim r As Long
On Error GoTo ErrHandler
' Get the row number
r = ActiveCell.Row
' Unprotect the sheet
ActiveSheet.Unprotect Password:="123"
' Insert a row
Range("N" & r).EntireRow.Insert
' Fill down the formula from above the inserted row to below it
Range("N" & (r - 1) & ":N" & (r + 1)).FillDown

'Insert ID - Assuming column A is number and not text
Dim MaxID As Integer
MaxID = Application.WorksheetFunction.Max(Range("A:A"))
ActiveCell.NumberFormat = "000"
ActiveCell = MaxID + 1

ExitHandler:
' Protect the sheet again
ActiveSheet.Protect Password:="123"
' Get out
Exit Sub

ErrHandler:
' Report the error to the user
MsgBox Err.Description, vbExclamation
' Always go past the exit handler section
Resume ExitHandler
End Sub

I think you could do this in an IF formula, using a mixture of LEFT, RIGHT and CONCATENATE.

For example, the below will replace the first three digits for you. You would have to create something similar to replace the other digits using MID instead of LEFT

=IF(LEFT($A1,3)="004",CONCATENATE("056",RIGHT($A1,8)),IF(LEFT($A1,3)="012",CONCATENATE("048",RIGHT($A1,8)),IF(LEFT($A1,3)="020",CONCATENATE("052",RIGHT($A1,8)),IF(LEFT($A1,3)="024",CONCATENATE("044",RIGHT($A1,8)),IF(LEFT($A1,3)="028",CONCATENATE("040",RIGHT($A1,8)),"")))))

Someone better than I will likely have a cleaner answer, and probably get both in the same formula. I am by no means fluent in this but hopefully it helps a bit

Hi, I just cut and pasted your formula into a clean excel sheet and it worked perfectly. It prevented all other letters and prevented in lower case. Have you tried in a clean sheet to rule out anything else conflicting with this?

Here it the current code.

Sub InsertARow()
Dim r As Long
On Error GoTo ErrHandler
' Get the row number
r = ActiveCell.Row
' Unprotect the sheet
ActiveSheet.Unprotect Password:="123"
' Insert a row
Range("N" & r).EntireRow.Insert
' Fill down the formula from above the inserted row to below it
Range("N" & (r - 1) & ":N" & (r + 1)).FillDown

ExitHandler:
' Protect the sheet again
ActiveSheet.Protect Password:="123"
' Get out
Exit Sub

ErrHandler:
' Report the error to the user
MsgBox Err.Description, vbExclamation
' Always go past the exit handler section
Resume ExitHandler
End Sub

Hi,

I have a spreadsheet with a Macro which identifies the Active Cell and inserts a new Row in that location. Column A of the Table is for the ID number.

I want the same Macro to self populate an ID in Column A of the new Row. I need it to be +1 higher than the maximum ID number already present in that column. In case it matters, Column A is using a Special Format (000) to ensure it always contains three digits. For example the number 3 will be shown as 003. Not sure if this matters.

As Rows will not always be at the bottom of the Table I cannot do a simple increment of the line above.

And this is where i am stuck as I am not very good at VBA. Any help would be appreciated.