Posts by hgus393

Hello all,
Using the solver function in a macro that I recorded returns "sub or function not Defiend". Does anybody know why?

Robert

Re: Financial Function : Price

Hello Infinity,
Filo65 is partially right. But Act/Act has a funny quirk to it considering all the future cash flows and the accrued part. I have had som issues with this myself and if you want I could post an example...
Robert

Re: Workbook change

Ok I am so impressed!
:wowee::wowee::wowee:
Thank you so much!
:thanx::yourock:

Robert

Re: Workbook change

Hum it does not seem to work for me. I put the macro in ThisWorkbook object but it only displays do you want to save changes and if I press yes then it brings up the same window again and again and again...
What am I doing wrong?
Robert

Hello all,
I am working in workbooks that are highly dependent on if any change is made in the workbook then the book needs to be saved in a new version. Is there a way to accomplish this without setting the book as read only?
Robert
:confused:

Re: Using DIM can't get it to copy all my data

Hello Ineedhelp,

Dont really know what you are trying to do,are you are trying to find a numerical value in column B where there are letters?!
Robert

Re: Saving files based on codes

Quote from jackie

How do I save a excel file based on codes in one excel file.

For e.g.

Name Location
Mr A UK
Mr.B US

Now I want to create a file US wherein all the data for US to be saved in one excel file.

One option is to manual apply filter and then save files individually

How to I automate this!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Display More

Why not use a vlookup to your data with two sepereate sheets one for US and one for UK?
Robert

Re: Seperating Text from numbers

And thank you very much for the functions
Robert

Re: Seperating Text from numbers

No there is just one hgus393, that is me, myself and I. Yeah it look very similar does it not.. I should have pasted the link to the previous post ..sorry!

Re: cut & paste

Ok what about this?

Sub TryThis()
Dim nxtrow As Long
Dim c As Range
Dim r As Range




Set c = Range("c1", Range("c65536").End(xlUp))
For Each r In c
Select Case r
Case Is = ""
Exit Sub
Case Is = 2
nxtrow = Range("D65536").End(xlUp).Row + 1
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column - 2)).Cut
ActiveSheet.Paste Destination:=ActiveSheet.Range("D" & nxtrow)
Case Is = 3
nxtrow = Range("g65536").End(xlUp).Row + 1
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column - 2)).Cut
ActiveSheet.Paste Destination:=ActiveSheet.Range("g" & nxtrow)
Case Is = 4
nxtrow = Range("j65536").End(xlUp).Row + 1
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column - 2)).Cut
ActiveSheet.Paste Destination:=ActiveSheet.Range("j" & nxtrow)
Case Is = 5
nxtrow = Range("m65536").End(xlUp).Row + 1
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column - 2)).Cut
ActiveSheet.Paste Destination:=ActiveSheet.Range("m" & nxtrow)
Case Is = 6
nxtrow = Range("p65536").End(xlUp).Row + 1
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column - 2)).Cut
ActiveSheet.Paste Destination:=ActiveSheet.Range("p" & nxtrow)
Case Is = 7
nxtrow = Range("s65536").End(xlUp).Row + 1
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column - 2)).Cut
ActiveSheet.Paste Destination:=ActiveSheet.Range("s" & nxtrow)
Case Is = 8
nxtrow = Range("v65536").End(xlUp).Row + 1
Range(Cells(r.Row, r.Column), Cells(r.Row, r.Column - 2)).Cut
ActiveSheet.Paste Destination:=ActiveSheet.Range("v" & nxtrow)
End Select
Next r


End Sub

Robert

Re: Seperating Text from numbers

Quote from Wigi

Data > Text to columns with a space as separator.

Or using the MID and the FIND function together. Find for the space and use the mid function to lift up the characters coming after the space.

=MID(A1,FIND(" ",A1)+1,100)

100 is just a large number to cover all characters coming after the space.

Or you the LEN function: number of characters in cell.

=MID(A1,FIND(" ",A1)+1,LEN(A1)-FIND(" ",A1))

Wigi

Display More

I think I spoke to soon, this works if there is a space between the letters and the numbers. What if there is no space? How could I seperate them then?

Robert

Re: Seperating Text from numbers

Thank you so much
Robert

Hi all!

Suppose I have a cell with for example ABC 123 in it. How would I move the 123 in to the adjacent (right) cell?

Robert

Re: Selection-First Cell

Hello,

Do you mean that in your Range("A8:C100") you want to find the first cell in this range that has a value?

Robert

Re: Removing text from a cell leaving only values

Quote from Andy Pope

Hi,

Here are a couple of ways. One tests each character the other uses each letter to test.[vba]Sub Macro1()
'
'
Dim intIndex As Integer
Dim strTemp As String

strTemp = ActiveCell.Value
intIndex = 1
Do While intIndex <= Len(strTemp)
Select Case UCase(Mid(strTemp, intIndex, 1))
Case "A" To "Z"
If intIndex > 1 Then
strTemp = Left(strTemp, intIndex - 1) & Mid(strTemp, intIndex + 1)
Else
strTemp = Mid(strTemp, intIndex + 1)
End If
Case Else
intIndex = intIndex + 1
End Select
Loop
ActiveCell.Value = strTemp

'
End Sub
Sub Macro2()

Dim lngIndex As Long
Dim strTemp As String

strTemp = ActiveCell.Value
For lngIndex = 65 To 90
strTemp = Application.WorksheetFunction.Substitute(UCase(strTemp), Chr(lngIndex), "")
Next
ActiveCell.Value = strTemp

End Sub[/vba]

Display More

Thank you ever so much - looking at your code makes me understand how little I understand ...

Thx Robert

Hi all,

Does anybody have a clue how I can remove all text (ie A through Z) from a cell, leaving only numerical values?

Robert

Re: copy, find, offset &amp; paste loop

Hi Gilbo and welcome,

I think I understand what you mean, but could you post an example of what you have so far?

Robert

Re: insert new sheets in numical order form a routine

Quote from toecutter

hi
when i run the code on sheet ORDER 000.001 IT INSERTS A NEW SHEET
Then when i run the code on sheet ORDER 000.001(2) same thing
so on and so on...ALLS GOOD

But when i run the code on sheet ORDER 000.001(3) it places this sheet in front of 000.001(2)

is there some code i can add to make it place the sheets in numical order EG
000.001(1), 000.001(2), 000.001(3), 000.001(4) so on and so on?

cheers
toe

Display More

Try this!

Private Sub Example_Click()


Sheets("ORDER 000.001").Copy After:=Sheets(Sheets.Count)


End Sub

Robert

Re: Copy and paste without clipboard to another book

Quote from Aaron Blood

Ya know, that is a curious example...

I'm not sure why Dave suggests to avoid copy/paste whenever possible as a means to speed up code. Maybe he can shed some light?

I've actually come across more examples to the contrary; where assigning range values to be equal is much slower than a copy/pastevalue.

Code

Sub test()


    'slower
    Sheet2.Range("A:E").Value = Sheet1.Range("A:E").Value
    
    'faster
    Sheet1.Range("A:E").Copy
    Sheet2.Range("A1").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False


End Sub

Display More

If anything, I'd say assigning values is a convenient code shortcut, but it certainly won't speed up a macro.

Display More

Yeah, noticed that too, ran a timer function on your example the first code (the slow one) ran in 0,5 seconds while the copy paste method ran in !! 0,0001 seconds??
Check it out:

Sub test()
     
    Dim Starttime As Date
    
Starttime = timer
     'slower
    Sheet2.Range("A:E").Value = Sheet1.Range("A:E").Value
    MsgBox Format(timer - Starttime, "00.0000") & "Seconds"
     
     Starttime = timer


     'faster
    Sheet1.Range("A:E").Copy
Sheet2.Range("A1").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False
         MsgBox Format(timer - Starttime, "00.0000") & "Seconds"
End Sub

??

Re: Copy and paste without clipboard to another book

A word of caution, both workbooks need to be open for this to work!
:roll:
Robert