Posts by smuzoen

Re: Select the last cell with a value and edit macro

I hope this helps. Without the actual excel sheet it is a little more difficult but the following should help you along
To dynamically find a range you could use code such as this

Dim lastRow As Integer
Dim lastCol As Integer
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
lastCol = Cells(1, Columns.Count).End(xlToLeft).Column
Dim xRange As Range
Set xRange = Range("A1", Cells(lastRow, lastCol))
xRange.Select

This will select the range you wish to copy no matter how many rows there are or how many columns assuming the data starts in cell A1

To past the values and their formats you would use something like this

Sheets("Data").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False

If this does not help upload the spreadsheet with some data and it would make things easier as I think in your code you have a lot of commands which you probably don't need. I assume this was from a macro recording?

Hope this helps but if not as I said upload a sample sheet and I will sort it out for you
Anthony

Re: code needed to calculate Sales Needed

In the sales need to hit target add the following formula

=IF(A2>0,(D2-C2/B2)*B2,"")

Copy the formula down as many rows as you need and this should do the job
Let me know if you need any further help
Anthony

Re: Hide rows based on date and colour

I have added the code to your claims spreadsheet - see if this works for you. I have tried it and it works for me.
Let me know

Option Explicit


Sub hide_green()
Dim thisMonth As Integer, compareMonth As Integer
thisMonth = Month(Now)
Dim Rng As Range
Dim MyCell As Range
Dim lastRow As Integer
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Set Rng = Range("A10:A" & lastRow)
For Each MyCell In Rng
compareMonth = Month(MyCell.Offset(0, 2))
If MyCell.Interior.ColorIndex = 50 And _
compareMonth <> thisMonth Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub

Hope this works
Anthony

Re: Hide rows based on date and colour

This should do what you want - hide rows where cell in column A is filled green and the date in column C is not in the current month.
You have done most of the work so your macro only needs a little tweek.

Option Explicit


Sub hide_green()
Dim thisMonth As Integer, compareMonth As Integer
thisMonth = Month(Now)


Dim Rng As Range
Dim MyCell As Range
Set Rng = Range("A2:A" & Range("A65000").End(xlUp).Row)
For Each MyCell In Rng
compareMonth = Month(MyCell.Offset(0, 2))
If MyCell.Interior.ColorIndex = 50 And _
compareMonth <> thisMonth Then
MyCell.EntireRow.Hidden = True
End If
Next MyCell
End Sub

Hope this helps
Anthony

Re: Multiply each cell in a range to another range

I think this is what you want - essentially multiply every number in column B by the first number in column A, then multiply every number in column B by second number in column A and so on - so no matter how long column A and column B is, every number in column A has to be multiplied by every number in column B and the numbers to be placed sequentially into column C.

Option Explicit


Sub multiplyArray()
Dim aList As Range, bList As Range
Dim aNum As Range, bNum As Range
Set aList = Range("A1:A" & Range("A65000").End(xlUp).Row)
Set bList = Range("B1:B" & Range("B65000").End(xlUp).Row)
Dim cAnswer As Integer
cAnswer = 1
For Each aNum In aList
For Each bNum In bList
Cells(cAnswer, 3) = aNum * bNum
cAnswer = cAnswer + 1
Next bNum
Next aNum
End Sub

I hope this is what you want
Anthony

Re: Find Text in String and Replace it with the other Text in the same String

You can unhide all the columns prior to executing the text substitution. The new code is as follows. As you see it only needs one more line of code.

Option Explicit


Sub ReplaceAllData()
 ActiveSheet.Cells.EntireColumn.Hidden = False
  Dim LastCol As Integer
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
   Dim k As Integer
   For k = 2 To LastCol
   'Replace all instances of text in Cell A1 with text in cell A2
   Columns(k).Replace Cells(1, 1), Cells(2, 1)
   Next k
End Sub

You can the hide the columns after doing the substitution if you want to using something like

Columns("C:C").Select
    Selection.EntireColumn.Hidden = True

Hope this is what you want
Anthony

Re: Search and retrieve information from columns in Excel into list view

I think this should do what you want - I have the code below for a form but I also have attached a macro enabled workbook so you can see how it works. Let me know if it needs to be altered or needs other functionality. It has a text box to enter your search term, a listbox for results and 2 buttons (search button and a cancel button) - open up the VBA editor and have a look at the code behind the form. There is some code in Workbook to load the form when the spreadsheet opens. I have some data in the sheet as well so you can try it out.

Option Explicit


Private Sub searchColumn_Click()
lbResults.Clear
If tbWordToSearch.Value = "" Then
MsgBox "Please type in search term"
Exit Sub
End If
Dim searchTerm As String
searchTerm = UCase(tbWordToSearch.Value)
Dim searchList As Range, findText As Range
Set searchList = Range("A1", "A" & Range("A65000").End(xlUp).Row)


For Each findText In searchList
If InStr(UCase(findText.Value), searchTerm) > 0 Then
lbResults.AddItem findText.Value
End If


Next findText


End Sub

There is some more code for the cancel button and some code to load the form on opening the spreadsheet.
Let me know if you need it modified. Hope it helps
Anthony

Re: VBA Editor word wrap for .Body text

If you have a long line of text you can wrap it in the editor by doing the following

bodyString = "Hello this is going to be a very long sentence so I want to wrap it so it does not continue so long in the editor"


'instead if having a long sentence you can do this


bodyString = "Hello this is going to be a very long sentence " & _
"so I want to wrap it so it does not continue " & _
"so long in the editor"


'to add a line break
bodyString = "Hello My name is John" & vbcrlf  & "What is your name"

With the hyperlink are you using the vba editor in excel or outlook? If you want outlook vba code you are in the wrong forum.
If you are after Outlook VBA help post it into the Excel and/or Email Help Forum http://www.ozgrid.com/forum/forumdisplay.php?f=10

Hope this helps
Anthony

Re: Find Text in String and Replace it with the other Text in the same String

This should do what you want - it will replace all instances of the the value in A1 with the values in A2 - so if the text in A1 occurs more than once in a specific cell it will replace all the instances of this. If I have not understood what you want just let me know

Option Explicit


Sub ReplaceAllData()


  Dim LastCol As Integer
    With ActiveSheet
        LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
    End With
   Dim k As Integer
   For k = 2 To LastCol
'Replace all instances of text in Cell A1 with text in cell A2
   Columns(k).Replace Cells(1, 1), Cells(2, 1)
   Next k
End Sub

Hope this helps
Anthony

Re: Copy Data from one sheet and insert to the end of a table on another sheet

You can paste as values but to preserve the format you will need to use the following

Option Explicit


Sub copyData()
Worksheets("Timetable").Select
Dim timeTableData As Range
Set timeTableData = Range("A2:O" & Range("A65000").End(xlUp).Row)
timeTableData.Select
Selection.Copy
With Sheets("Data").Range("A65000").End(xlUp).Offset(1, 0)
    .PasteSpecial xlPasteFormats
    .PasteSpecial xlPasteValues
End With
End Sub

Is that what you need? If you dont keep the xlPasteFormats command you will lose the format of your dates and times in the spreadsheet. If I am missing want you want let me know in more detail.
Hope this is what you want
Anthony

Re: Order list of dates and adjacent values using formula

When you say you need a formula I assume you mean a macro? The following macro will sort your list into date order.

Option Explicit


Sub sortData()
Dim dateRange As Range
Set dateRange = Range("A1:B" & Range("A65000").End(xlUp).Row)
dateRange.Select


ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A1:A" & Range("A65000").End(xlUp).Row) _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:B" & Range("A65000").End(xlUp).Row)
        .Header = xlGuess
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With




End Sub

Open up Visual Basic Editor (on developer tab) and insert a module. Paste the code into the editor window then close the editor. To run the macro select Macros in the developer tab and select the sortData macro and run it.

Hope this helps
Anthony

Re: Copy Data from one sheet and insert to the end of a table on another sheet

The process is fairly much the same. For this to work it assumes that the second workbook is open at the same time as the first workbook. Just substitute the workbook name for the second workbook into the following code.

Option Explicit


Sub copyDataToAnotherBook()
Worksheets("Timetable").Select
Dim timeTableData As Range
Set timeTableData = Range("A2:O" & Range("A65000").End(xlUp).Row)
timeTableData.Select
Selection.Copy
'Substitute Another Data.xlsx with the name of the second workbook
'This second workbook must be open at the same time
Windows("Another Data.xlsx").Activate
Sheets("Data").Select
Dim lastRow As Integer
lastRow = Range("A65000").End(xlUp).Row + 1
Cells(lastRow, 1).Select
ActiveSheet.Paste
End Sub

Hope this helps
Anthony

PS: If the other workbook is closed you can do the following

Workbooks.Open Filename:="C:\Path to second workbook.xlsx", _
        UpdateLinks:=0

Re: Copy Data from one sheet and insert to the end of a table on another sheet

The following code will do what you want.

Option Explicit


Sub copyData()
Worksheets("Timetable").Select
Dim timeTableData As Range
Set timeTableData = Range("A2:O" & Range("A65000").End(xlUp).Row)
timeTableData.Select
Selection.Copy
Sheets("Data").Select
Dim lastRow As Integer
lastRow = Range("A65000").End(xlUp).Row + 1
Cells(lastRow, 1).Select
ActiveSheet.Paste
End Sub

Hope this helps
Anthony

Re: Convert Text to Number within a column

Lets assume you have the following in column A
$491.80M
$2.06B
The following code will convert the text in Column A and put the real value in Column B. The assumptions are that each revenue entry starts with a "$" and ends with with "B" or "M"

Option Explicit


Sub convTextToNumbers()
'Assume the worksheet with data is in sheet called Revenue
Worksheets("Revenue").Activate
Dim lastRow As Integer, k As Integer
lastRow = Range("a65000").End(xlUp).Row
Dim revenueLength As Integer
Dim realValue As Double


For k = 1 To lastRow
revenueLength = Len(Cells(k, 1))
'strip away the $ and the ending B or M
realValue = Mid(Cells(k, 1), 2, revenueLength - 2)
'Assume only conditions are B or M
If Right(Cells(k, 1), 1) = "B" Then
realValue = realValue * 1000000000
End If
If Right(Cells(k, 1), 1) = "M" Then
realValue = realValue * 1000000
End If
'write real value to column B - change 2 to 1 to overwrite text and replace with real number
Cells(k, 2) = realValue
realValue = 0
Next k
end sub

I hope this helps
Anthony

PS: You could do this without VBA using the following conditional statement within the worksheet

=IF(RIGHT(RC[-1],1)="M",MID(RC[-1],2,(LEN(RC[-1])-2))*1000000,MID(RC[-1],2,(LEN(RC[-1])-2))*1000000000)

If Column 1 contains e.g. $2.06M then this formula would convert it to 2060000. This assumes there only two conditions ->B or M

Just another option