VB:Hope someone finds it useful.Option Base 0 Public Function Str_2d(str As String, intCol, Optional Delim As String = " ") As Variant 'convert a string to a 2 dimensional array - using space as default delimiter. Dim Num_Rows As Long Dim arrTemp, arrTemp2 Dim iCount As Integer, Row_Count As Integer, Col_Count As Integer Num_Rows = Application.RoundUp((Len(str) - Len(Replace(str, Delim, "")) + 1) / intCol, 0) 'determine size and shape of resulting array - number of rows for number columns arrTemp = Split(str, Delim): iCount = 0 'icount is the index for arrtemp and that is a zero based array Redim arrTemp2(Num_Rows - 1, intCol - 1) For Row_Count = 1 To Num_Rows For Col_Count = 1 To intCol arrTemp2(Row_Count - 1, Col_Count - 1) = Trim(arrTemp(iCount)) iCount = iCount + 1 If iCount > UBound(arrTemp) Then Exit For Next Next Str_2d = arrTemp2 End Function Public Sub driver() Dim x ActiveSheet.Cells.Clear x = Str_2d("This is a sweet function for 2 dimensional arrays Ha! Ha", 3) 'or 'x = Str_2d("This is a sweet function^for 2 dimensional arrays^Ha! Ha", 3, "^") 'or 'x = Str_2d("This is a sweet,function for 2,dimensional,arrays,Ha! Ha", 1, ",") 'display the result.... ActiveSheet.Range("A1").Resize(UBound(x, 1) + 1, UBound(x, 2) + 1) = x End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Dim continue As Boolean continue = True If continue = True Then MsgBox "Boolean variables are cool"However, I'd like to say something like the following.
Dim tocontinue1 As Boolean Dim tocontinue2 As Boolean 'choose is only a string that I want to change to represent the tocontinue1 Boolean choose = "tocontinue1" If Z = 1 Then choose = True If tocontinue1 = True or Then MsgBox "Boolean variables are cool"Any Help is welcome.
ActiveCell.FormulaR1C1 = "=sum(MyRange)"but at the end of the macro I need to delete name reference so that the code can run next time for a different search string without any problem.
My problem is how do I convert MyRange to the actual cell reference from what we know which is:
(i) The cells to be summed are always in F column.
(ii) The Active Cell is already one row below the range to be summed.
(iii) The number of rows is stored in a variable called Counter
Basically what I need is Sum(R-[Counter]C:R-C)
I know this must be very simple for you veterans but I am stumped. Thank you for your help.
VB:So how do I get the variable a to act like a string. When I attempt to add to another string in excel I receive an error.'a is being read from a cell in excel that is a date a = "2/3/2006"If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Private Sub Client_Click() Sheets("Client-Template").Select End SubI need help to make the sheet reference dynamically linked to the contents of Cell C8 which will have the exact worksheet name of any of the clients:
Private Sub Client_Click() Client as String Client = Range(C8) Sheets("Client").Select End SubThank you for any and all of your help!
VB:myMonth() Dim strMonth As Month, dMonth As Date strMonth = Sheet32.Range("colMillMonth").Cells(1).Value dMonth = Format(Right(strMonth, 3), "mmmm") End SubIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:This code gives a runtime error 1004, because the Blank and the Dot are "not allowed" in the string here. If the code is changed to the following, it runs fine (sure both times the correct workbook is open when the code runs).my_book_macro = "Book No.1!my_macro" Application.Run my_book_macroIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
VB:Thanks for any suggestion to solve this.my_book_macro = "Book_No1!my_macro" Application.Run my_book_macroIf you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines