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:
ABCD
11131700633xxxabc10
21
aaa1a1
31131700634xxxabc10
42
aaa2a2
51131700635xxxabc11
61
aaa3a3

<tbody>
</tbody>


What it should look like
ABCDEFG
11131700633
xxxabc10aaa1a1
21131700634xxxabc10aaa2a2
31131700635xxxabc11aaa3a3

<colgroup><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
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:

1131700628
xxxa44600604516/19/2013 7:011128151.59C210061A0773113- D130.049400460A166 330 02 001.66E+21Z5022H210061A0773113- D130.049
5A0773113-33.4560260389A004540000+277.500mm+ 11000N+276.500mm+ 0N+283.000mm+ 12500N+282.000mm+ 0N+295.000mm+ 1001N
1131700629xxxa44600604516/19/2013 7:021128151.59C210061A0773143- D130.049400460A166 330 02 001.66E+21Z5022H210061A0773143- D130.049
5A0773143-33.5554260389A004540100+277.500mm+ 11000N+276.500mm+ 0N+283.000mm+ 12500N+282.000mm+ 0N+295.000mm+ 1001N
1131700630xxxa44600604516/19/2013 7:031128151.61C210061A0773153- D130.057400460A166 330 02 001.66E+21Z5022H210061A0773153- D130.057
5A0773153-33.4573260389A004539800+277.500mm+ 11000N+276.500mm+ 0N+283.000mm+ 12500N+282.000mm+ 0N+295.000mm+ 1001N
1131700631xxxa44600604516/19/2013 7:031128151.63C210061A0773133- D130.065400460A166 330 02 001.66E+21Z5022H210061A0773133- D130.065
5A0773133-3.053.5540260389A004539000+277.500mm+ 11000N+276.500mm+ 0N+283.000mm+ 12500N+282.000mm+ 0N+295.000mm+ 1001N
1131700632xxxa44600604516/19/2013 7:041128151.57C210061A0773123- D130.048400460A166 330 02 001.66E+21Z5022H210061A0773123- D130.048
5A0773123-3.13.519260389A004539200+277.500mm+ 11000N+276.500mm+ 0N+283.000mm+ 12500N+282.000mm+ 0N+295.000mm+ 1001N
1131700633xxxa44600604516/19/2013 7:051128151.61C210061A0770713- D130.043400460A166 330 02 001.66E+21Z5022H210061A0770713- D130.043
5A0770713-
33.5511260389A004539100+277.500mm+ 11000N+276.500mm+ 0N+283.000mm+ 12500N+282.000mm+ 0N+295.000mm+ 1001N

<colgroup><col><col><col><col><col><col span="11"></colgroup><tbody>
</tbody>
 
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

L
  • Question
Count of unique sub-entries
Replies
3
Views
132
dreid1011
dreid1011
T
  • Question
Add rows above cells containing specific string.
Replies
0
Views
166
topi1
T
R
  • Question
Macro For Comparison Of Data Matching
Replies
1
Views
124
rajendradk9
R
N
  • Question
VBA increment number sequence based on 2 cell values
2
Replies
13
Views
943
kevin9999
K
drom
  • Question
Validation list based on cell value
Replies
5
Views
168
mumps
mumps

Forum statistics

Threads
1,214,919
Messages
6,122,259
Members
449,075
Latest member
staticfluids

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