IF / VBA help!

H

heman334

New Member
Joined
Jun 20, 2013
Messages
6
Hey, all i need to do is if column "A" has the number "1", "2", or "3", then select "B2 TO D2" cut it, paste at E1, THEN delete row 2. Etc....

See below:

My problem:
[TABLE="width: 407"]
<tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1131700633[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]abc[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]aaa[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]a1[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1131700634[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]abc[/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]aaa[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]a2[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]1131700635[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]abc[/TD]
[TD="align: center"]11[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]aaa[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]a3
[/TD]
[/TR]
</tbody>[/TABLE]


What it should look like
[TABLE="width: 711"]
<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"]1131700633
[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]abc[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]aaa[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]a1[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"]1131700634[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]abc[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]aaa[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]a2[/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"]1131700635[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]abc[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]aaa[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]a3[/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
heman334,

Thanks for the workbook.

Sample raw data in worksheet raw (not all columns are shown for brevity):


Excel 2007
ABCDNOPQ
1Unit NumberLocationOperation TypeTimestampHousing pipe serial number (Barcode)Housing Set (serial number)Output Shim Size L.H. Calculated [mm]Output Shim Size R.H. Calculated [mm]
21131700628xxxa6/19/2013 7:01S12061353
35A0773113-360+286.000mm+ 1000N+308.001mm+ 26000N
41131700629xxxa6/19/2013 7:02S12061353
55A0773143-354+286.000mm+ 1000N+308.001mm+ 26000N
61131700630xxxa6/19/2013 7:03S12061353
75A0773153-373+286.000mm+ 1000N+308.001mm+ 26000N
82131700636xxxStation worked OK6/19/2013 7:07A00586430G3.253.65
92131700637xxxStation worked OK6/19/2013 7:07A00586530G3.253.6
102131700638xxxStation worked OK6/19/2013 7:08A00586630G3.23.7
1111703G0970xxxStation worked OK6/19/2013 11:17S12061361
125A0650923C2.955+292.000mm+ 1000N+312.001mm+ 34000N
135
1411703G0971xxxStation worked OK6/19/2013 11:17S12061361
155A0650863C2.9545+292.000mm+ 1000N+312.001mm+ 34000N
165
1711703G0972xxxStation worked OK6/19/2013 11:18S12061361
185A0650853C2.9524+292.000mm+ 1000N+312.001mm+ 34000N
195
20
raw


After the macro:


Excel 2007
ABCDNOPQ
1Unit NumberLocationOperation TypeTimestampHousing pipe serial number (Barcode)Housing Set (serial number)Output Shim Size L.H. Calculated [mm]Output Shim Size R.H. Calculated [mm]
21131700628xxxa6/19/2013 7:01S12061353A0773113-360
31131700629xxxa6/19/2013 7:02S12061353A0773143-354
41131700630xxxa6/19/2013 7:03S12061353A0773153-373
52131700636xxxStation worked OK6/19/2013 7:07A00586430G3.253.65
62131700637xxxStation worked OK6/19/2013 7:07A00586530G3.253.6
72131700638xxxStation worked OK6/19/2013 7:08A00586630G3.23.7
811703G0970xxxStation worked OK6/19/2013 11:17S12061361A0650923C2.955
911703G0971xxxStation worked OK6/19/2013 11:17S12061361A0650863C2.9545
1011703G0972xxxStation worked OK6/19/2013 11:18S12061361A0650853C2.9524
11
12
13
14
15
16
17
18
19
20
raw


Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Code: 
Option Explicit
Sub ReorgData()
' hiker95, 06/21/2013
' http://www.mrexcel.com/forum/excel-questions/709737-if-visual-basic-applications-help.html
Dim r As Long, lr As Long, lc As Long, rng As Range
Application.ScreenUpdating = False
With Sheets("raw")
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  For r = 2 To lr Step 1
    If .Cells(r, 3) >= 1 And .Cells(r, 3) < 4 Then
      lc = .Cells(r, .Columns.Count).End(xlToLeft).Column
      Set rng = .Range(.Cells(r, 2), .Cells(r, lc))
      rng.Copy Destination:=.Range("O" & r - 1)
      rng.ClearContents
    End If
  Next r
  On Error Resume Next
  .Range("B2:B" & lr).SpecialCells(xlCellTypeBlanks).EntireRow.Delete
  On Error GoTo 0
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the ReorgData macro.
 
Upvote 0

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Put this formula in E1 =IF(MOD(ROW(),2)=1,B2,"") then copy E1 to F1 and G1. Copy (E1:G1) until the end of your data set. Select column E:G copy paste special values, sort your data set (A:G) and clear blank cells.
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Solved
Count of consecutive months same data value appears
Replies
1
Views
187
rhonllanders
R
L
  • Question Question
Count of unique sub-entries
Replies
3
Views
287
dreid1011
dreid1011
E
  • Solved
Find a value matching a range upon several criteria
Replies
2
Views
156
ED38
E
T
  • Question Question
Add rows above cells containing specific string.
Replies
0
Views
347
topi1
T
R
  • Question Question
Macro For Comparison Of Data Matching
Replies
1
Views
286
rajendradk9
R

Forum statistics

Threads
1,223,236
Messages
6,170,917
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top