Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with messy formula
The formula below works okay, but is a mixture of code which might cause some
problems for me down the road (and is probably not doing what I need it to do very efficiently); I also ultimately need to setup something to copy the data to the 'merge' worksheets which will be used by Word. Yes, there is a reason the worksheets must be setup this way... if it helps, the workbook (with fictional data) is on my Microsoft Office Live website: http://suzleigh.com/MERGE.aspx (any comments/advice on how I can improve anything in here would be GREATLY appreciated) The formula below is linked to a command button 1. Copy cells containing data from PERSONNEL (columns A through I) to IHSF DATA ENTRY (columns B through J) 2. Reduce the data in column I (SSN) to last 4 3. Return the focus to B2 Sub GetIHSFData() Sheets("PERSONNEL DATA").Select Range("A2:J500").Select Selection.Copy Sheets("IHSF DATA ENTRY").Select Range("B2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False LastRow = Cells(Rows.Count, "I").End(xlUp).Row For RowCount = 2 To LastRow Data = Trim(Range("I" & RowCount)) If Len(Data) = 4 Then Data = Trim(Right(Data, 4)) End If If IsNumeric(Data) Then Number = Val(Data) End If Range("I" & RowCount) = Number Next RowCount Range("B2").Select End Sub Thanks VERY much (also many thanks to everyone who got me to this point) Suzanne |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with messy formula
Hi Suzanne
I would do it like that: ------------------------------------------------- Sub GetIHSFData() Sheets("PERSONNEL DATA").Range("A2:J500").Copy With Sheets("IHSF DATA ENTRY") .Range("B2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False For RowCount = 2 To .Cells(.Rows.Count, "I").End(xlUp).Row Data_i = Right(Trim(.Range("I" & RowCount).Value), 4) If IsNumeric(Data_i) Then Number_I = Val(Data_i) End If .Range("I" & RowCount) = Number_I Next RowCount .Select .Range("B2").Select End With End Sub ----------------------------------------------------------------- I would not use Data and Number as variablenames, they may be used by VBA. I deleted the selecting of the cells, because it's unnecessary. hope thats what you wanted Carlo On Dec 10, 2:27 pm, Suzanne wrote: The formula below works okay, but is a mixture of code which might cause some problems for me down the road (and is probably not doing what I need it to do very efficiently); I also ultimately need to setup something to copy the data to the 'merge' worksheets which will be used by Word. Yes, there is a reason the worksheets must be setup this way... if it helps, the workbook (with fictional data) is on my Microsoft Office Live website: http://suzleigh.com/MERGE.aspx (any comments/advice on how I can improve anything in here would be GREATLY appreciated) The formula below is linked to a command button 1. Copy cells containing data from PERSONNEL (columns A through I) to IHSF DATA ENTRY (columns B through J) 2. Reduce the data in column I (SSN) to last 4 3. Return the focus to B2 Sub GetIHSFData() Sheets("PERSONNEL DATA").Select Range("A2:J500").Select Selection.Copy Sheets("IHSF DATA ENTRY").Select Range("B2").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False LastRow = Cells(Rows.Count, "I").End(xlUp).Row For RowCount = 2 To LastRow Data = Trim(Range("I" & RowCount)) If Len(Data) = 4 Then Data = Trim(Right(Data, 4)) End If If IsNumeric(Data) Then Number = Val(Data) End If Range("I" & RowCount) = Number Next RowCount Range("B2").Select End Sub Thanks VERY much (also many thanks to everyone who got me to this point) Suzanne |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Messy Formula needs clean up help! | Excel Worksheet Functions | |||
VBA not working - Messy. | Excel Worksheet Functions | |||
Messy Users | Excel Programming | |||
Messy tab characters | Excel Discussion (Misc queries) | |||
Messy Text to Columns | Excel Discussion (Misc queries) |