Copy one value to all empty cell in one column of another sheet

jocker_boy

Board Regular
Joined
Feb 5, 2015
Messages
83
Hi,

I have a table in sheet "BoM" with values between "A:H", with the column "I" and "J" empty, only with header.

I'm trying to copy one value ("D2") from sheet "teste":

VBA Code:
Sheets("teste").Select
Range("D2").Select
Selection.Copy

To paste that value to sheet "BoM" on column "I" to all empty cells in this column until the last row of the table "A:H".

And then repeat to copy the value from "E2" from sheet "teste" to the column "j".

Many thanks,
Gonçalo
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Ok, how about
VBA Code:
Sub jockerboy()
   With Sheets("BoM")
      .Range("I:I").SpecialCells(xlBlanks).Value = Sheets("teste").Range("D2").Value
      .Range("J:J").SpecialCells(xlBlanks).Value = Sheets("teste").Range("E2").Value
   End With
End Sub
 
Upvote 0
Solution
Ok, how about
VBA Code:
Sub jockerboy()
   With Sheets("BoM")
      .Range("I:I").SpecialCells(xlBlanks).Value = Sheets("teste").Range("D2").Value
      .Range("J:J").SpecialCells(xlBlanks).Value = Sheets("teste").Range("E2").Value
   End With
End Sub
Thanks for your help.
But i get this error message:

Compile error:
Expected: expression
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,539
Messages
6,114,221
Members
448,554
Latest member
Gleisner2

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