View Full Version : index if VBA
Popup
10-06-2011, 08:55 AM
Hello,
I'm relatively new to VBA and tried searching this forum, but can't find a solution to my problem. Hope you can help me. Here is my problem; I am trying to create a macro that will get the name in row#1 if it has a corresponding data.
Please see attached example.
Thanks..:help :help
Bob Phillips
10-06-2011, 09:35 AM
Public Sub ProcessData()
Dim Lastrow As Long
Dim Lastcol As Long
Dim i As Long, ii As Long
Application.ScreenUpdating = False
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
Lastcol = .Cells(1, .Columns.Count).End(xlToLeft).Column
For i = Lastrow To 3 Step -1
For ii = Lastcol To 2 Step -1
If .Cells(i, ii).Value = "x" Then
.Rows(i + 1).Insert
.Cells(i + 1, "A").Value = .Cells(i, "A").Value
.Cells(i + 1, "B").Value = .Cells(1, ii).Value
End If
Next ii
.Rows(i).Delete
Next i
.Columns("C").Resize(, Lastcol - 2).Delete
.Rows("1:2").Delete
End With
Application.ScreenUpdating = True
End Sub
Popup
10-06-2011, 05:01 PM
Hi,
Thankyou very much for your help, but this code is not working. It is deleting entries in my raw data table. Please help..
Thanks:hi:
Bob Phillips
10-07-2011, 01:33 AM
In my test the Results were exactly as you show.
omp001
10-07-2011, 11:35 AM
maybe this way
Sub CopyData()
Dim lastRow, lastCol, nextRow, i, ii As Long
With ActiveSheet
nextRow = 13
lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
lastCol = .Cells(3, .Columns.Count).End(xlToLeft).Column
For i = 5 To lastRow
For ii = 2 To lastCol
If .Cells(i, ii).Value = "x" Then
nextRow = nextRow + 1
.Cells(nextRow, "A") = .Cells(i, 1).Value2
.Cells(nextRow, "B") = .Cells(3, ii).Value2
End If
Next ii
Next i
End With
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.