(Hopefully) Simple Array Pasting question.

[KeLee]

Hello MVP's

An initial Caveat, I know almost nothing about VBA

I have, laboriously, created this code to check validation in my workbook
and highlight any cells in certain ranges that conflict with the allowed
validation.

I want to do this as i have an automated routine that pastes values into
these ranges from my CRM software, which can override the validation rules
set for manual entry - this is fine as I just then need users to update a few
conflicts.

So far I have this

Sub SetInvalidCellsRedAndStore()
Dim CACompiler(540) As String
Dim SheetCompiler(540) As String
ArrayCell = 0
For Each Sheet In Sheets
Sheet.Activate
Calculate
For Each Cell In Range("D10:E29")
ArrayCell = ArrayCell + 1
If Cell.Validation.Value = False Then
Cell.Interior.ColorIndex = 3
Cell.Font.Bold = True
CACompiler(ArrayCell) = Cell.Address
SheetCompiler(ArrayCell) = Sheet.Name
Else:
End If
Next Cell
Next Sheet
End Sub

Which at the end gives me two arrays each 540 items long with values in if
the corresponding cell matched the criteria i.e. failed validation checks.
all the other entries will be blank, that is not ideal, but OK.

All I want to do now is paste the contents of those two arrays into another
sheet.

I want the values of "SheetCompiler" to go into a sheet called "Datastore"
in the range A2:A541

And similarly, the values of "CACompiler" to go into the same sheet,
"Datastore" in the range B2:B541

But I can't even get close to getting it right despite having looked up
about 20 examples. I couldn't figure out how to customise them to my
requirements.

Any help with this is most appreciated.

Regards,
KeLee

 

[Hemant_india]

try this
worksheets("datastore").activate
with activesheet
for n= 1 to ubound(caccomplier)
cells(n,1)=caccomplier(n)
next n
same u can use for your other array
if bothe array elemnts are matching
u can insert
cells(n,2)=sheetcomplier(n) also

 

[Bernie Deitrick]

KeLee,

It is pretty easy - all you need to do is set the value of a range equal to the value of your array
(actually, using transpose, but that is because you want a column). But you don't need to make such
a big array to start, or record two pieces of information. See my modifications below.

HTH,
Bernie
MS Excel MVP

Sub SetInvalidCellsRedAndStore()
Dim SheetCompiler() As String
Dim cell As Range
Dim arraycell As Integer
arraycell = 0
For Each Sheet In Sheets
Sheet.Activate
Calculate
For Each cell In Range("D10:E29")
If cell.Validation.Value = False Then
arraycell = arraycell + 1
ReDim Preserve SheetCompiler(1 To arraycell)
cell.Interior.ColorIndex = 3
cell.Font.Bold = True
SheetCompiler(arraycell) = cell.Address(, , , True)
End If
Next cell
Next Sheet

With Worksheets("Datastore")
.Range("A:A").ClearContents
.Range("A1").Value = "Bad Validations"
.Range("A2").Resize(arraycell).Value = _
Application.Transpose(SheetCompiler)
End With
End Sub

 

[Hemant_india]

hi KeLee
i forgt to put End With in my earlisr post
with activesheet
my code=====
end with

 

[KeLee]

Thankyou, that is exactly what I wanted, you even tideid up my extraneous data.

I tried lots of times to rate this post in reply, but it won't let me.
Hopefully this might prompt for the usefulness of your feedback.

Regards,
KeLee

 

[Bernie Deitrick]

KeLee,

Thanks for the feed back - you can't rate my post because I post directly to the newsgroup, not
through the discussions web interface.

Bernie
MS Excel MVP