Hello,
We receive a text file as output and import it into Excel to be formatted. We are trying to automate the formatting, but I don't understand how to run my function from the spreadsheet. I can run it from the VBA editor by pressing F5, but I need to know how to do it from the worksheet. The idea is I am going to hand off this function to co-workers and I need to show them how to run it. Does a function require and argument to be passed to it? This is my 1st time writing a VBA. Any advice would be amazing. Thanks!
The function gets the size of the number of columns, from that number moves backwards to delete the empty columns. Then it iterates down each row, while it is in each row it checks the value of one of the columns and if it meets the requirements concatenates that to a stored location with a comma in between, etc..... (formatting)
[VBA] Function FormatSpreadsheet()
Dim x As Integer
Dim RowPos As Integer
Dim ColPos As Integer
Dim i As Integer
Dim Last As Long
Dim j As Long
Dim LastRow As Long
Dim Las As Long
Dim k
'Finds the last column in the Range: gives column count
Last = Cells.Find("*", SearchOrder:=xlByColumns, _
LookIn:=xlValues, SearchDirection:=xlPrevious).Column
'MsgBox Last
'Deletes the empty columns in the Range
For j = Last To 5 Step -3
Columns(j).Delete
Next
'Find the last used row in column A: gives row count
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With
'MsgBox LastRow
'Selects the 1st cell
Range("B2").Select
' Iterating DOWN the Rows
For x = 1 To LastRow
RowPos = ActiveCell.Row
ColPos = ActiveCell.Column
'MsgBox RowPos & "," & ColPos
i = x + 1
'Find the last used column in a Row: row 1 in this example
Dim LastCol As Integer
With ActiveSheet
LastCol = .Cells(i, .Columns.Count).End(xlToLeft).Column
End With
LastCol = (LastCol - 2) / 2
'MsgBox LastCol
' Iterating ACROSS a single Row
For y = 1 To LastCol
ActiveCell.Offset(0, 1).Select
Dim rPos
Dim cPos
Dim ErPos
Dim EcPos
'If the first Allele column in numeric and not X or Y
If Not IsNumeric(ActiveCell.Value) Then
Else
'stores the concatenation site
If y = 1 Then
rPos = ActiveCell.Row
cPos = ActiveCell.Column
End If
'store the sites 'to be' concatenated
If y <> 1 Then
ErPos = ActiveCell.Row
EcPos = ActiveCell.Column
End If
'move to the height column
ActiveCell.Offset(0, 1).Select
Height = ActiveCell.Value
If Not IsNumeric(ActiveCell.Value) Then
'MsgBox "not numeric" | do nothing
End If
'if it is the 1st height and it is under 100 then clear the cell's contents else do nothing
If y = 1 And Height < 50 Then
Cells(rPos, cPos).ClearContents
End If
If y = 1 And Height > 50 And Height < 99 Then
Cells(rPos, cPos).Font.Color = &HC0C0C0
End If
'if is not the 1st height and it is equal to or greater than 100, concatenate allele current site to the 'base' allele site
If y <> 1 And Height >= 100 Then
'concatenate allele to cells(rPos, cPos)
Cells(rPos, cPos) = Cells(rPos, cPos) & "," & Cells(ErPos, EcPos)
End If
End If
Next
Cells(RowPos, ColPos).Select
' Selects cell down 1 row from active cell.
ActiveCell.Offset(1, 0).Select
Next
'Finds the last column in the Range: gives column count
Las = Cells.Find("*", SearchOrder:=xlByColumns, _
LookIn:=xlValues, SearchDirection:=xlPrevious).Column
'MsgBox Last
'Deletes the left over columns
For k = 4 To Las
Columns(4).Delete
Next
End Function
[/VBA]
Reply With Quote
