Excel Macros.

[Torniquet]

Ok i cannot seem to find anything that helps me do what i want to do.

and due to my serious lack of VB knowlege, i thought i would ask here

ok situation is this...

at work, we need to get a load of information from one application and paste it into excel to make a CVS file. Once it has been pasted in, some information needs to be changed.

Changing some information is easy, because its is as straight forward as using the replace function (which has been recorded into the macro)

now the last part i cannot get working, which is that i need the text in every cell of column E to have the character count reduced to display only the 1st 8 letters/numbers.

but everything i seem to try just keeps spitting errors back at me lol

any hlp would be appreciated

would like to get this done ready for work on monday ^^ xxx

[Torniquet]

ok after more faffing about... i think i have nearly done it lol XD

only problem is i cant get the bastard to stop when it hits an empty cell

it just runs though all 65k cells in the column ¬¬

Code: Select all

Sub Macro3()
'
' Macro3 Macro
'

'
    Columns("A:A").Select
    For Each cell In Selection
    If IsEmpty(ActiveCell) = False Then
    cell.Value = Left(cell.Value, 5)
    Else
    End If
    Next cell
End Sub

[Jackolantern]

Both of these sound like they could be done pretty easily through a formula. Is there something in there that can't be done with one? For example, to get just the first 8 letters you could write the formula

Code: Select all

=left(d2, 8)

...in e2, copy it all the way to the bottom, and now the E column would have the first 8 letters of the D column, so the D column could be deleted. Replacing based on a condition is also quite easy. I can, however, see an interest in having a macro you can use later. But VBA is just...uggh lol

[Torniquet]

No i cannot use a formula for this

it needs to be set out just right lol

Column A needs to hold specific information.
Column B needs to hold specific information.
Column C needs to hold specific information.
Column D needs to hold specific information.
Column E needs to hold specific information.

Nothing else must be contained on this excel sheet..

but certain information that gets pasted into excel needs to be altered to stop another program we use spitting back errors.

[Torniquet]

Whey i figured it out with a lil help from my friend google lol.

Code: Select all

Sub Macro4()
'
' Macro4 Macro
'

'
    Columns("A:A").Select
    Selection.Replace What:="laptop", Replacement:="Notebook", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="pc", Replacement:="System", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Selection.Replace What:="other", Replacement:="External Devices", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("B:B").Select
    Selection.Replace What:="no barcode", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Columns("D:D").Select
    Selection.Replace What:="*", Replacement:="1", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False
    Range("E1").Select
        Do
        If IsEmpty(ActiveCell) = False Then
        ActiveCell.Value = Left(ActiveCell.Value, 8)
        End If
        ActiveCell.Offset(1, 0).Select
        If ActiveCell = "" Then
        Exit Do
        End If
        Loop Until ActiveCell.Value = " "
End Sub

how do u like them apples mofo!!