What cell is my data coming from?

[Solved/Closed]
Report
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
-
Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
-
Oh dear - back again ... :^(

After you guys help again with VBA.

I'm never going to volunteer to try script-writing again! (at least not without one to one tuition in a formal training environment and a 20 year course behind me).

OK - I have this code (I have commented what I want where I need it) ....

Sub FillMeIn()

x = 1

For Each cell In Range("A1:D14").SpecialCells(xlCellTypeBlanks)
cell.Value = Sheets("Source").Range("G7")

' Here I want to copy the contents of the cell to the left of the cell just written to and paste it to cell "H7" on the "Source" sheet.

If x = 1 Then Exit For

Next cell

End Sub


What this does is find all the empty cells in the range A1-D14, and then fills in an empty cell with a value from another sheet [cell "G7" on sheet "Source"] which is in the same workbook.

What I need is the contents of the cell to the left of the cell just written to [one of those in the range "A1:D14"] to be used to fill a cell on another sheet [cell "H7" on sheet "Source"].

I know the loop sort of cancels itself after one itteration due to the x value - this was designed for this question so I can see how the solution code works, and will let me add the solution easily to the project I have been unfortunate enough to take on.


I really do appreciate your help - I am learning a lot from this, but I think I'll stick to hardware fault diagnosis in future.


Brian.

2 replies

Posts
341
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
67
you really need to explain more ...
and you have to put in mind that there is no cell to the left of the column A:A, since your range starts from A1 and ends on D14
as on how to determine the left cell simply implement this method
Cells(ActiveCell.Row, ActiveCell.Column - 1)
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
136
Thanks Max,

I did know about there are no cells to the left of column A but this is fully occupied with the data I need to copy - the first column with an empty cell is (should be) always in column B so fortunately the issue cannot arise - and if it does then it would highlight an error in the spreadsheet setup which must then be addressed manually. There would be an error handler built in so this will not be an issue ... I say that with lots more confidence than I actually have! (but even without an error handler the macro will stop with an error)

Perhaps Im being thick here, but I dont know how to make myself clearer on this. Basically I get a value from a sheet called "Source" from cell G7. The code I have then places it into the first blank cell on a different sheet (lets call it "Target" for this example) which is the active sheet - for this examples sake, lets assume the empty cell is C3. All this so far I have achieved with the script I put earlier.

Now the bit im stuck on is ..
I need to copy the data in the cell next to where I have just pasted (in this example sheet "Target" cell B3) and paste it into cell H7 on sheet "Source".

I believe I have come up with a chunky solution, but I'm sure it can be refined. I have incorporated your line and it seems to work on my test spreadsheet. Im not sure how scailable this is though - only the Gods know at this stage.

Sub FillMeIn()
Sheets("Source").Select
x = 1

For Each cell In Range("A1:D14").SpecialCells(xlCellTypeBlanks)
cell.Value = Sheets("Source").Range("G7")

Cells(cell.Row, cell.Column - 1).Select

Selection.Copy
Sheets("Target").Select
Range("G7").Select
ActiveSheet.Paste
Sheets("Source").Select

If x = 1 Then Exit For

Next cell

End Sub

Thanks for your help, but if you can think of a better way then please let me know. (I do know that it is possible to manipulate sheets without making tham active and then switching back again, but its late and I need that bed that is calling me from afar.

Thanks Again,

Brian.
Posts
341
Registration date
Tuesday March 3, 2015
Status
Moderator
Last seen
July 3, 2015
67
I have the solution for u !!!!
but you need to tweak it up to fit your project.
I assumed that there would be multiple empty cells in column B and the values copied to the source sheet from the left cell of the one written by the code ...... bla bla bla .... you'll understand the code here it is, if you need further explanation I'm glad to keep up !!

Sub go()
      Dim i, j As Integer
         i = 1
         j = 0
    Do While ActiveSheet.Cells(i, 1).Value <> ""
        If ActiveSheet.Name = "source" Then
          Exit Do
        End If
        If ActiveSheet.Cells(i, 2).Value = "" Then
        ActiveSheet.Cells(i, 2).Value = Sheet1.Cells(7, 7).Value
        Sheet1.Cells(7 + j, 8).Value = ActiveSheet.Cells(i, 1).Value
        j = j + 1
    End If
    i = i + 1
    Loop
MsgBox ("List ended on cell A" & Cells(i, 1).Row)
End Sub


dreaming is just a waste of time.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1004
Registration date
Saturday January 17, 2015
Status
Moderator
Last seen
November 27, 2020
136
Superb!

Thanks Max.

A combination of both of your comments and melding it into the code I already have has done the job nicely and allowed me to rid myself of several lines of code just in that bit that was holding me back. I can also see where your code can replace lots of my coding in other areas of my script, so even more of my awful code can go!

Thank you so much for your help. (but I'm still not volunteering for any more coding jobs!).

Unfortunately the project is still not complete, but the end is in sight. Hopefully no more coding questions form me ... ever!

Thanks again.

Brian.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!