Help with Macro

[tonyvee1973]

Hi All

Have a spreadsheet with empty cells in which i need to fill with specified values.
I have used the formula below to add zero's to empty cells in the specified range.

Sub Fill_Blanks()
Dim MyCell As Range
For Each MyCell In Range(Selection.Address)
If MyCell = vbNullString Then
MyCell.Value = 0
End If
Next
End Sub

This works great but now i have another issue.
In one column i have a list of codes duplicated several times and now i need to add another 6 codes to the list.

Is there any way i can adapt the formula above to allow me to specify the 6 codes i need to add and get them in the empty spaces i have created for them?

I have tried variations of below but this doesnt work.

Sub Fill_Blanks()
Dim MyCell As Range
For Each MyCell In Range(Selection.Address)
If MyCell = vbNullString Then
MyCell.Value = 55400, 55401, 55402, 55403, 55404, 55405
End If
Next
End Sub

Any help really would be appreciated

Thanks

 

[Andrzejek]

Do you measn something like this?

Sub Fill_Blanks()
Dim MyCell As Range
Dim i As Long

i = 55400

For Each MyCell In Range(Selection.Address)
  If MyCell = vbNullString Then
    MyCell.Value = i
    i = i + 1
  End If
Next

End Sub

Have fun.

---- Andy

 

[tonyvee1973]

Hi Andy

Yes that seems the sort of thing i'm looking for but i have 6 spaces in a column and i need to insert the numbers 55400, 55401, 55402, 55403, 55404, 55405 in the empty cells that i have made in the column. This range is repeated several times in the sheet so need the macro to look for the 6 empty cells in the sheet whereever they are and add in the range of these 6 numbers
would the macro you suggest do this or just add the one number

 

[Loomah]

not really sure what you are trying to do
the following will list each of your values in each empty cell - ie all 6 codes in one cell.

Sub Jugated()
Dim strIpped As String
strIpped = "55400" & Chr(10) & "55401" & Chr(10) & "55402" & Chr(10) & "55403" & Chr(10) & "55404" & Chr(10) & "55405"
Selection.SpecialCells(xlCellTypeBlanks) = strIpped
End Sub

if you need different values in different cells what are your 'rule' for doing so? what is the criteria for puting 55400 in one cell and 55401 in a different one?

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

ok, note to self "check what has happened in a thread while you've been messing around before posting and you might not end up looking like you're asking stupid questions of the original poster"

noted!!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[N1GHTEYES]

Loomah: Sub Jugated()?

Chuckles quietly.

Tony

 

[SkipVought]

Sub Fill_Blanks()
  Dim MyCell As Range, i as integer
  Const _NUM = 55400
  
  For Each MyCell In Selection
    If MyCell = vbNullString Then
      MyCell.Value = _NUM + i
      i = i + 1
      if i > 5 then i = 0
    End If
  Next
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Andrzejek]

Example (please correct me if I am wrong):
Instead of 6 we have here 3 numbers
[tt]
A B C D E
1 2 4 3 6
2 4 [highlight] [/highlight] 5
3 [highlight] [/highlight] 6 9
4 4
[/tt]

And you want to fill the highlighted cells with your number, right? Rows 2 and 3

If so, how about row 4 - you have more than required number of empty cells. Do you fill them or not? And if you do, where do you start?

Have fun.

---- Andy

 

[SkipVought]

tony,

We're ALL guessing at what you INTEND.

Let's start "In the beginning..."

Without referring to VBA or code of any kind, please tell us WHAT you want to accomplish, not HOW you think it ought to be done. Give lots of descriptive information, please.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[tonyvee1973]

Hi All

Sorry for the confusion, i'll try to explain below.

My list looks like this in excel (See attached)

Firstly in the left hand yellow column (Column E) i need to insert the following numbers in sequence where there is gaps (55401,55407,55403,55404,55405,55406) this is what i meant by my post above.

But now i need to do something else also after that.
I need to move any values apart from zeroes from the right hand yellow column (Column H) into the left hand column (Column E). If there is a zero value in column H then i dont want to move the value across. Can this be done?

I know this is a big ask and now a "Double hit" from my original post but any help really would be appreciated.

Thanks as always

 

[tonyvee1973]

apologies, in my second question when i'm trying to move values from column H i need to move to Column F NOT column E

Sorry

 

[SkipVought]

1. Why a WORD file, if you're working in Excel???

2. Then the value sequence is totally DIFFERENT than what you have been referring to in your previous posts???

I am afraid, that for me, you have lost credibility.

I need for you to state the business case for this exercise. If this is all there is, why don't you just enter those values manually??? If there is more than this, you have not begun to explain your purpose, and how this extends to other occurrences.

Ball's in your court!

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]