VBA Macro Column counts and Rows

[apatriarca87]

Hi,

I'm a beginner to excel and I'm creating a macro that will move values to column B and AI. Here's the scenario I have to check each row and with each row either have two columns that have values or one columns with values the values can either be in any ranges of columns. I know I need to loop each row after I move the values i need.

If row = 1 column with values then
move value to same row column C.

If row = 2 columns with values then
move value furthest to the right
to same row column AI.

Sub MoveValue()
Dim val1 as string
Dim val2 as string
Dim workrange as Range

Set r = Sheets("Sheet1").UsedRange

For r = 1 to workrange

If row = 1 column with values then
move value to same row column C.

If row = 2 columns with values then
move value furthest to the right
to same row column AI.

next r


End Sub

 

[bruderbell]

Can you post some before and after data?

 

[apatriarca87]

hi bruderbell I just want to say thank you for taking the time to reply I really appreciate it.

Here's a before and after picture
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting
after:
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting

 

[bruderbell]

Do you really want a macro to do this? You can accomplish your goal with formulas:

B2=IF(INDEX(E2:T2,MAX((COLUMN(E2:T2)-COLUMN(E2)+1)*ISNUMBER(--RIGHT(E2:T2,4))))=0,"",INDEX(E2:T2,MAX((COLUMN(E2:T2)-COLUMN(E2)+1)*ISNUMBER(--RIGHT(E2:T2,4)))))
AI2=IF(INDEX(E2:T2,MATCH("*",E2:T2,0))=B2,"",INDEX(E2:T2,MATCH("*",E2:T2,0)))

Both entered as arrays (ctrl shift enter). Then drag them down to fill the column.
Essentially column B will return the "right-most" (farthest right) value where the last four characters are numbers, or return a blank if no values meet that criteria. Column AI will return the "left-most" value that doesn't match the value in the same row, column B.

If you really want a macro, that won't be too difficult. Let me know.

 

[apatriarca87]

hi bruderbell
your code gave me and idea and i created a macro to move just the farthest right value. My new problem is that I have to Cut and Past the values rather than copying it. I keep getting this error "Run-time error '1004'" pastespecial method of Range class failed. Thank you so much for you help!

Here's my code
Dim workrange As Range
Dim CellCount As Long


Set workrange = Sheets("Sheet1").UsedRange
For r = 2 To workrange.Rows.Count
'Set CellCount to 0 for each row
CellCount = 0
For c = 1 To workrange.Columns.Count + 1
'Select each cells as it searches
Cells(r, c).Select
'Check for cells with values
If Cells(r, c).Value > 0 Then
'Count cells with values
CellCount = CellCount + 1
'Get the last data count
If CellCount = 2 Then

'cut and move data to column "AI"
ActiveCell.Copy
Cells(r, 1).Activate
ActiveCell.Offset(0, 34).PasteSpecial


End If

End If

Next c

Next r


End Sub

 

[bruderbell]

your code gave me and idea and i created a macro to move just the farthest right value.

But isn't your goal to move data to the left and right? Is the prior formula not working for you?