Page 1 of 1
Excel Macros.
Posted: Sat Apr 10, 2010 6:10 am
by 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
Re: Excel Macros.
Posted: Sat Apr 10, 2010 6:47 am
by 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
Re: Excel Macros.
Posted: Sat Apr 10, 2010 5:07 pm
by 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
...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
Re: Excel Macros.
Posted: Sat Apr 10, 2010 6:29 pm
by 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.
Re: Excel Macros.
Posted: Sun Apr 11, 2010 10:02 am
by 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!!