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]
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
Code: 
Sub sdffds()
    Dim i As Integer
    
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If (Trim(Cells(i, 1).Value) Like "#") Then
            Cells(i, 5).Offset(-1).Value = Cells(i, 2).Value
            Cells(i, 6).Offset(-1).Value = Cells(i, 3).Value
            Cells(i, 7).Offset(-1).Value = Cells(i, 4).Value
            Rows(i & ":" & i).Delete Shift:=xlUp
        End If
    Next
End Sub
 
Last edited:
Upvote 0
pman said:
Code: 
Sub sdffds()
    Dim i As Integer
    
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        If (Trim(Cells(i, 1).Value) Like "#") Then
            Cells(i, 5).Offset(-1).Value = Cells(i, 2).Value
            Cells(i, 6).Offset(-1).Value = Cells(i, 3).Value
            Cells(i, 7).Offset(-1).Value = Cells(i, 4).Value
            Rows(i & ":" & i).Delete Shift:=xlUp
        End If
    Next
End Sub
Click to expand...

wow, that's amazing, but i was hoping i can just pick up from what you were doing. Here's is my actual problem, it actually keep going on to COLUMN "DA". Hope that makes sense?

Here it is:

[TABLE="width: 1196"]
<colgroup><col><col><col><col><col><col span="11"></colgroup><tbody>[TR]
[TD="align: center"]1131700628
[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]4460060451[/TD]
[TD="align: center"]6/19/2013 7:01[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2815[/TD]
[TD="align: center"]1.59[/TD]
[TD="align: center"]C210061A0773113- D130.049[/TD]
[TD="align: center"]400460[/TD]
[TD="align: center"]A166 330 02 00[/TD]
[TD="align: center"]1.66E+21[/TD]
[TD="align: center"]Z5022[/TD]
[TD="align: center"]H210061A0773113- D130.049[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A0773113-[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3.45[/TD]
[TD="align: center"]60[/TD]
[TD="align: center"]260389A004540000[/TD]
[TD="align: center"]+277.500mm[/TD]
[TD="align: center"]+ 11000N[/TD]
[TD="align: center"]+276.500mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+283.000mm[/TD]
[TD="align: center"]+ 12500N[/TD]
[TD="align: center"]+282.000mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+295.000mm[/TD]
[TD="align: center"]+ 1001N[/TD]
[/TR]
[TR]
[TD="align: center"]1131700629[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]4460060451[/TD]
[TD="align: center"]6/19/2013 7:02[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2815[/TD]
[TD="align: center"]1.59[/TD]
[TD="align: center"]C210061A0773143- D130.049[/TD]
[TD="align: center"]400460[/TD]
[TD="align: center"]A166 330 02 00[/TD]
[TD="align: center"]1.66E+21[/TD]
[TD="align: center"]Z5022[/TD]
[TD="align: center"]H210061A0773143- D130.049[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A0773143-[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3.55[/TD]
[TD="align: center"]54[/TD]
[TD="align: center"]260389A004540100[/TD]
[TD="align: center"]+277.500mm[/TD]
[TD="align: center"]+ 11000N[/TD]
[TD="align: center"]+276.500mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+283.000mm[/TD]
[TD="align: center"]+ 12500N[/TD]
[TD="align: center"]+282.000mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+295.000mm[/TD]
[TD="align: center"]+ 1001N[/TD]
[/TR]
[TR]
[TD="align: center"]1131700630[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]4460060451[/TD]
[TD="align: center"]6/19/2013 7:03[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2815[/TD]
[TD="align: center"]1.61[/TD]
[TD="align: center"]C210061A0773153- D130.057[/TD]
[TD="align: center"]400460[/TD]
[TD="align: center"]A166 330 02 00[/TD]
[TD="align: center"]1.66E+21[/TD]
[TD="align: center"]Z5022[/TD]
[TD="align: center"]H210061A0773153- D130.057[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A0773153-[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3.45[/TD]
[TD="align: center"]73[/TD]
[TD="align: center"]260389A004539800[/TD]
[TD="align: center"]+277.500mm[/TD]
[TD="align: center"]+ 11000N[/TD]
[TD="align: center"]+276.500mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+283.000mm[/TD]
[TD="align: center"]+ 12500N[/TD]
[TD="align: center"]+282.000mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+295.000mm[/TD]
[TD="align: center"]+ 1001N[/TD]
[/TR]
[TR]
[TD="align: center"]1131700631[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]4460060451[/TD]
[TD="align: center"]6/19/2013 7:03[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2815[/TD]
[TD="align: center"]1.63[/TD]
[TD="align: center"]C210061A0773133- D130.065[/TD]
[TD="align: center"]400460[/TD]
[TD="align: center"]A166 330 02 00[/TD]
[TD="align: center"]1.66E+21[/TD]
[TD="align: center"]Z5022[/TD]
[TD="align: center"]H210061A0773133- D130.065[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A0773133-[/TD]
[TD="align: center"]3.05[/TD]
[TD="align: center"]3.55[/TD]
[TD="align: center"]40[/TD]
[TD="align: center"]260389A004539000[/TD]
[TD="align: center"]+277.500mm[/TD]
[TD="align: center"]+ 11000N[/TD]
[TD="align: center"]+276.500mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+283.000mm[/TD]
[TD="align: center"]+ 12500N[/TD]
[TD="align: center"]+282.000mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+295.000mm[/TD]
[TD="align: center"]+ 1001N[/TD]
[/TR]
[TR]
[TD="align: center"]1131700632[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]4460060451[/TD]
[TD="align: center"]6/19/2013 7:04[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2815[/TD]
[TD="align: center"]1.57[/TD]
[TD="align: center"]C210061A0773123- D130.048[/TD]
[TD="align: center"]400460[/TD]
[TD="align: center"]A166 330 02 00[/TD]
[TD="align: center"]1.66E+21[/TD]
[TD="align: center"]Z5022[/TD]
[TD="align: center"]H210061A0773123- D130.048[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A0773123-[/TD]
[TD="align: center"]3.1[/TD]
[TD="align: center"]3.5[/TD]
[TD="align: center"]19[/TD]
[TD="align: center"]260389A004539200[/TD]
[TD="align: center"]+277.500mm[/TD]
[TD="align: center"]+ 11000N[/TD]
[TD="align: center"]+276.500mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+283.000mm[/TD]
[TD="align: center"]+ 12500N[/TD]
[TD="align: center"]+282.000mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+295.000mm[/TD]
[TD="align: center"]+ 1001N[/TD]
[/TR]
[TR]
[TD="align: center"]1131700633[/TD]
[TD="align: center"]xxx[/TD]
[TD="align: center"]a[/TD]
[TD="align: center"]4460060451[/TD]
[TD="align: center"]6/19/2013 7:05[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]2815[/TD]
[TD="align: center"]1.61[/TD]
[TD="align: center"]C210061A0770713- D130.043[/TD]
[TD="align: center"]400460[/TD]
[TD="align: center"]A166 330 02 00[/TD]
[TD="align: center"]1.66E+21[/TD]
[TD="align: center"]Z5022[/TD]
[TD="align: center"]H210061A0770713- D130.043[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]A0770713-
[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]3.55[/TD]
[TD="align: center"]11[/TD]
[TD="align: center"]260389A004539100[/TD]
[TD="align: center"]+277.500mm[/TD]
[TD="align: center"]+ 11000N[/TD]
[TD="align: center"]+276.500mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+283.000mm[/TD]
[TD="align: center"]+ 12500N[/TD]
[TD="align: center"]+282.000mm[/TD]
[TD="align: center"]+ 0N[/TD]
[TD="align: center"]+295.000mm[/TD]
[TD="align: center"]+ 1001N[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Can you post an actual sample sheet? I will look at it in the morning if someone else does not before then.
 
Upvote 0
heman334,

Here's is my actual problem, it actually keep going on to COLUMN "DA".
Click to expand...

Your new raw data is nothing like your original post. And, we can not tell what cells, rows, columns, your new raw data is in.

To get it right this next time:

You can upload your workbook to Box Net,
Sheet1 should contain your raw data, and worksheet Results should contain the results from Sheet1 (manually formatted by you that you are looking for
mark the workbook for sharing
and provide us with a link to your workbook.
 
Upvote 0
hiker95 said:
heman334,



Your new raw data is nothing like your original post. And, we can not tell what cells, rows, columns, your new raw data is in.

To get it right this next time:

You can upload your workbook to Box Net,
Sheet1 should contain your raw data, and worksheet Results should contain the results from Sheet1 (manually formatted by you that you are looking for
mark the workbook for sharing
and provide us with a link to your workbook.
Click to expand...

here is the document:
https://dl.dropboxusercontent.com/u/54050864/sample.xlsx
 
Upvote 0
heman334,

Please do not quote entire replies from your helper. When quoting follow these guidelines:
1. Quote ONLY if it is needed to add clarity or context for your reply. If so, then
2. Quote ONLY the specific part of the post that is relevant - - not the entire post.

This will keep thread clutter to a minimum and make the discussion easier to follow.


Thanks for the workbook, but:

Sheet1 should contain your raw data, and worksheet Results should contain the results from Sheet1 (manually formatted by you that you are looking for).

Please supply another workbook with the two worksheets mentioned above.
 
Upvote 0
Try this:

Code: 
Sub sdffds()
    Dim i As Integer, j As Integer
    
    For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
        If (Trim(Cells(i, 1).Value) Like "#") Then
            If (Cells(i, 2).Value = "") Then
                Rows(i).Delete
            Else
                For j = 2 To Cells(1, Columns.Count).End(xlToLeft).Column
                    Cells(i, j).Offset(-1, 13).Value = Cells(i, j).Value
                Next
                Rows(i).Delete
            End If
        End If
    Next
End Sub
 
Upvote 0
It didn't work, it just kept looping in the "else" loop. Kept focusing on cell (i,j), where value of i is always 34, and value of j keeps going down increments of +1.

But even then, it didn't cut and paste or delete the rows.
 
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,229
Messages
6,170,881
Members
452,364
Latest member
springate

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